Date comparison formula

Posted by Bryan on October 14, 2000 11:22 AM

I'm trying to compare a start date and an end date to the months of 2000 and put the relevant days against each month e.g.
Start End Jan Feb Mar...Dec
25/1/2000 25/12/2000 7 29 31 25

Using formulas can anyone help?

Posted by Tim Francis-Wright on October 16, 2000 1:52 PM

This doesn;t pay attention to whether the
dates are in the same year or not... but
the logic to extend it isn't too hard.

This assumes that:
A3 = first date
B3 = second date
C2:N2 = 1 to 12 (you can set up a custom
number format to make these show as months,
or have C1 etc be =date(2000,C2,1) with
format mmm (and hide row 2)

C3 = CHOOSE(SIGN(MONTH($A3)-C2)+2,CHOOSE(SIGN(MONTH($B3)-C2)+2,0,$B3-EOMONTH($B3,-1),DATE(2000,C2+1,1)-DATE(2000,C2,1)),MIN($B3-$A3,EOMONTH($A3,0)-$A3+1),0)
then copy this over to N3

I used CHOOSE here to eliminate some IF
statements; the SIGN functions generate 1, 2, or
3 depending on whether the date in question
is before, during, or after the month in question.

Also, if A2>B2, there will be a signle negative
answer.

HTH!

Posted by Bryan on October 17, 2000 3:04 PM

Thank you for the formula, it works a treat, but not quite sure why!