If formula and dates

[Excelophobe]

Hi all,

It is time i came across a new excel issue, and i've always found this forum to be the most helpful.

My issues is:

I have a column of dates spanning a year or so. I have created a column beside the dates column and I need a formula that categorises each date as either Over 60 Days, Over 90 Days, or Over 120 Days.

Basically i need each date to have one of those three categories attached to it in an adjacent column.

Can anyone help?

Thanks in advance.

 

[Excelophobe]

Test doc attached.

Regards.

 

[Marc L]

Hi !

B2 formula :
​

=IF(TODAY()-A2>120,$G$4,IF(TODAY()-A2>90,$G$3,IF(TODAY()-A2>60,$G$2,"")))

Copy down …
​

 

[Excelophobe]

Awesome! Thanks Marc L.

 

[Marc L]

Thanks !

I removed my second try 'cause was not so good …

​

 

[Khalid NGO]

Hi,
Another way is to use lookup.

Instead of text "Over 60 Days" just enter number 60,90,120 in column G

B2:
=LOOKUP(TODAY()-A2,$G$2:$G$4)

Format cell as:
"Over "#" Days"

copy down.

or see the attached sheet 2

Regards,

 

[Deepak]

One more!

="Over " & LOOKUP(TODAY()-A2,{0,60,90,120},{0,60,90,120}) &" Days"

 

[Khalid NGO]

One more!

="Over " & LOOKUP(TODAY()-A2,{0,60,90,120},{0,60,90,120}) &" Days"

Sir can we omit the [result_vector]?
="Over " & LOOKUP(TODAY()-A2,{0,60,90,120}) &" Days"

 

[Deepak]

Sir can we omit the [result_vector]?
="Over " & LOOKUP(TODAY()-A2,{0,60,90,120}) &" Days"

Good!!