IF function to return a message depending on month and year

[Garetht2014]

Hi,

I am trying to work out how to use an IF function to put in the correct message I want to see.

I have a Start Date , Todays Date & Time Served column. In column K would like to populate that with a value.

So if the Time served is under Six months I would like the cell in Column K to show 0-6, 6 months to a year to show 6-12.

I thought I cracked it but for some reason it has put some of the cells that are over 6 months in the 0-6 category which isn't correct.

Anyone able to point me in the right direction please ?

 

[welshgasman]

Need to work on the actual number of months, not text.

 

[Fluff]

Is column J a formula? If so what.

 

[Garetht2014]

Column J is:
=DATEDIF(H3,I3,"y")&" years "&DATEDIF(H3, I3,"ym")&" months "

Welshgasman:

I don't understand what you mean. Could you elaborate a bit for me please ?

 

[Fluff]

Thanks for that.
How about

=LET(m,DATEDIF(H3,I3,"m"),IF(m<=6,"0-6",IF(m<=12,"6-12",">12")))

 

[welshgasman]

Column J is:
=DATEDIF(H3,I3,"y")&" years "&DATEDIF(H3, I3,"ym")&" months "

Welshgasman:

I don't understand what you mean. Could you elaborate a bit for me please ?

You need to test for the number of months, not some text that has a number in it.
So....use a similar calculation as you show above to calculate how many actual months there are.
I do not have DateDIF in 2007 but would hazard a guess it would be

DATEDIF(H3, I3,"m")

 

[Fluff]

I do not have DateDIF in 2007

Yes you do, It's been around forever. It just isn't listed.

 

[welshgasman]

Yes you do, It's been around forever. It just isn't listed.

So I do.
Thank you Fluff.

 

[Garetht2014]

Thank you Fluff

That worked a treat

 

[Fluff]

Glad we could help & thanks for the feedback.