If formula

[kumar0318]

Hello- I have a date column in column A, and I need to build an "IF" formula based on the following conditions:

0- days if its future date
1- If its less than 30 days
2 - if its less than 60 days
3- If its less than 120 days
4- if its more than 120 days

 

[Joe4]

Less than 30 days from what?
Another date or the current date?
And is that 30, 60, 120 days in the past?

 

[kumar0318]

less than the date in the date column. Here is what the date column looks like:

Estimated In-Service-Date (Depreciation starts)

3/31/2023​

10/1/2022​

7/31/2021​

12/31/2021​

3/31/2023​

2/28/2023​

10/30/2022​

9/30/2022​

9/30/2022​

9/30/2022​

9/30/2022​

9/30/2022​

9/30/2022​

5/13/2020​

7/5/2022​

5/31/2022 ​

 

[kumar0318]

lets say I'm working on a current month, which is March. Anything after match 31 would be 0. Anything between March ist to March 31st would be 1, Anything from Feb 1 to March 31st is 2

 

[Joe4]

So, are you just comparing the current month to the month of the date (which could be a bit different than going by the number of days)?
If you are doing a date comparison, we need to know exactly what date you want to compare it do:
- the current date (i.e. 3/15)
- the first day of the current month (i.e. 3/1)
- last last day of the current month (i.e. 3/31)

Also, which version of Excel are you trying to do this on? 365 or 2007?
There are some really cool new functions in 365 that might make things easier, but I don't want to suggest them if you aren't using that version.

 

[kumar0318]

Yes, that's correct. I'm using 2007 excel version!

 

[Joe4]

Can you please answer my other question?

So, are you just comparing the current month to the month of the date (which could be a bit different than going by the number of days)?
If you are doing a date comparison, we need to know exactly what date you want to compare it do:
- the current date (i.e. 3/15)
- the first day of the current month (i.e. 3/1)
- last last day of the current month (i.e. 3/31)

Your answer needs to be one of those three options I listed.
You will get different results based on which one you choose, so it makes a big difference!

 

[kumar0318]

- the current date (i.e. 3/15)
- the first day of the current month (i.e. 3/1)
- last last day of the current month (i.e. 3/31)

Yes- that's correct

 

[Joe4]

- the current date (i.e. 3/15)
- the first day of the current month (i.e. 3/1)
- last last day of the current month (i.e. 3/31)

Yes- that's correct

No, its not!
You need to pick just ONE (and EXACTLY one) of those three options!
For this calculation, which date do you want to compare the dates on your sheet to?

Can you please answer that question?

 

[kumar0318]

Oh sorry- then it would be current date which is 03/15