DATEDIF function

[C.S.]

I need a datedif formula to look at the months and days between two dates and display only the months. However, i need the result (number of months) to be rounded up if the days are 15 or more and rounded down if 14 or less. right now i'm using this formula, but i'm not sure how or where to alter:
=DATEDIF(G6,H6,"y")&" years "&DATEDIF(G6,H6,"ym")&" months "&DATEDIF(G6,H6,"md")&" days"

also, is the datedif function a useable function in older versions of excel? (2003, xp, etc.) I'm using 2007.

thanks,
cs

 

[delaneyjm]

Maybe:

=DATEDIF(G6,H6,"y")&" years "&if(DATEDIF(G6,H6,"md")>15,DATEDIF(G6,H6,"ym")+1&" months", DATEDIF(G6,H6,"ym")&" months, "&DATEDIF(G6,H6,"md")&" days"

Datedif first appeared in Excel 2000 so, you should be good to go with versions prior to Excel 2007.

 

[Norie]

CS

DATEDIF is actually only included for backwards compatibility, it's been around as long as Excel.

Perhaps even longer, I think I remember some link back to Lotus 123.

Back to the question - what days are you referring to?

 

[Jonmo1]

Try

=DATEDIF(G6,H6,"y")&" years "&DATEDIF(G6,H6,"ym")+(DATEDIF(G6,H6,"md")>14)&" months"

However, this can get odd if the roundup makes it go to 12 months..

You'll get 2 years 12 months, instead of 3 years...

 

[C.S.]

for example:

Start date: 12/28/2008
End date: 4/17/2009
result: 3 months 19 days

what I want it to display: 4 months

 

[C.S.]

i'm really only concerned with the months, the maximum month count would never go above 12, so jon, i think that emliminates your concern. Your formula seems to work beautifully, however, is there a way that I can have it only display the month count (with the roundups/downs accounted for)? Is there a way to 'hide' the years portion? It will always just display "0 years", which I know someone, inevitably, will ask me if that needs to be there (@ work).

 

[C.S.]

i guess a =right(A1,9) function works, but is there another way to do it straight away in the datedif formula?

 

[Jonmo1]

Are you saying you want a total of the months...

like instead of 2 years 5 months, do you want 29 months or 5 months?

If you litterally only want the months, just drop the first part that get's the years

=DATEDIF(G6,H6,"y")&" years "&DATEDIF(G6,H6,"ym")+(DATEDIF(G6,H6,"md")>14)&" months"

Change it to
=DATEDIF(G6,H6,"ym")+(DATEDIF(G6,H6,"md")>14)&" months"

 

[C.S.]

Are you saying you want a total of the months...
like instead of 2 years 5 months, do you want 29 months or 5 months?

jon,
correct. only months, like i said though, it won't go over 12 ever. However, i'm using the formula, and when the count should be exacly 12 months, it displays 0 months. I think because the 'year' portion is taken out of the formula.

Dates being used where problem arises:
start: 12/28/2008 (G6)
end: 12/28/2009 (H6)
formula being used:
=DATEDIF(G6,H6,"ym")+(DATEDIF(G6,H6,"md")>14)&" months"
result: 0 months

formula with 'year' portion intact did the same thing, kinda:
=DATEDIF(G6,H6,"y")&" years "&DATEDIF(G6,H6,"ym")+(DATEDIF(G6,H6,"md")>14)&" months"
result: 1 year 0 months

 

[barry houdini]

Perhaps try

=DATEDIF(G6,H6+15,"m")&" months"