Dates.. dates... how about this formulas ??

Posted by Juan Pablo on November 16, 2001 12:16 PM

I'm not happy when a formula doesn't work the way it should do, so i'm proposing this one so you can tell me if it works a little better, and we can end the "saga" ... (Start in B1, end in C1)

Juan Pablo

For years:
=IF(AND(MONTH(C1)>=MONTH(B1),DAY(C1)>=DAY(B1)),YEAR(C1)-YEAR(B1),IF(AND(MONTH(C1)>MONTH(B1),DAY(C1)&LT;DAY(B1)),YEAR(C1)-YEAR(B1),YEAR(C1)-YEAR(B1)-1))

For months:
=IF(AND(MONTH(C1)>=MONTH(B1),DAY(C1)>=DAY(B1)),MONTH(C1)-MONTH(DATE(YEAR(C1),MONTH(B1),DAY(B1))),IF(AND(MONTH(C1)>MONTH(B1),DAY(C1)&LT;DAY(B1)),MONTH(C1)-MONTH(DATE(YEAR(C1),MONTH(B1),DAY(B1)))-1,IF(AND(MONTH(C1)<MONTH(B1),DAY(C1)>=DAY(B1)),12-MONTH(B1)+MONTH(C1),12-MONTH(B1)+MONTH(C1)-1)))

For days:
=IF(DAY(C1)>=DAY(B1),DAY(C1)-DAY(B1),DAY(C1)+DATE(YEAR(C1),MONTH(C1),1)-1-DATE(YEAR(C1),MONTH(C1)-1,DAY(B1)))

It involves the use of the Date() function, maybe that helps.

Juan Pablo

Posted by Juan Pablo on November 16, 2001 12:17 PM

Corrected post

Posted by Barrie Davidson on November 16, 2001 12:23 PM

Juan, I'm not yet convinced that my solution doesn't work. Can you give me an example (using actual dates) where it doesn't?

Regards,
BarrieBarrie Davidson

Posted by Juan Pablo on November 16, 2001 1:10 PM

To be perfectly honest Barrie, i haven't tested yours... i just don't like working with dates...

Anyway, i have one question (I was reading again all of the posts). You say, when using start Nov 29 2000 and end 27 Feb 2001, that from Nov29 to 30 there's one day, and from 1 Feb to 27 Feb there's 27.

I'm confused by this, because in the first one you're doing a mixed interval "( ]" or "[ )", including first date and excluding last or the other way, but in the other one, you're doing a closed interval "[ ]", including first and including last, right ? now.... why ? i think that from 29Nov00 to 27Feb01 there should be 29 days (29,30 of Nov, and 1 through 27 of Feb)...

There are actually 91 days (Including both ends), that is:
Nov: 2
Dec: 31
Jan: 31
Feb: 27
Total: 91

Your formula results in 2 months (I agree, Dec and Jan) and 28 days (You're doing the mixed range, here, right ?)

Juan Pablo

Posted by Barrie Davidson on November 16, 2001 1:18 PM

Okay, I can agree with that. I guess it really depends on the application; I can think of some cases where you don't want to count November 29 as a day. Anyway, if you want to include November 29 you can accomodate this by changing my formula to (adding "+1" to the IF statement for days):

=YEAR(B1)-YEAR(A1)+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))&" years, "&MONTH(B1)-MONTH(A1)+MIN(0,SIGN(DAY(B1)-DAY(A1)))+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))*-12&" months, "&IF(DAY(B1)-DAY(A1)&LT;0,EOMONTH(A1,0)-A1+DAY(B1),DAY(B1)-DAY(A1))+1&" days"

Thanks Juan.
Regards,
Barrie

Barrie Davidson