In an Excel 2010 spreadsheet I have the following


Cell A1 = 17/06/2010 11:55:43 PM
Cell A2 = 17/06/2010 11:58:43 PM
Cell A3 = 18/06/2010 12:01:43 AM

Cell B1 = 1
Cell B2 = =IF(DATEDIF(A2,A1,"d")=0,B1,B1+1)
Cell B3 = =IF(DATEDIF(A3,A2,"d")=0,B2,B2+1)

The formula in cell B2 returns the correct answer 1, but cell B3 returns a #NUM! error. It should be 2. I think it is because the entry in cell A3 is the next day to cell A3

How do I get the formula to work in cell B3?

I manually enter 1 into cell B2. I then want to look at the next cell in column A. If the date, excluding the time) is the same as the previous cell in column A, then the corresponding cell in column B is the same. If not, I want to add 1 to the previous cell in column B

Thanks in advance

I believe this may be because DATEDIF insists on the first argument being less than or equal to the second argument. You get away with it in the B2 formula because you're comparing days so Excel ignores everything after the '17/06/2010'.

If you change B3 to "=IF(DATEDIF(A2,A3,"d")=0,B2,B2+1)" then you get 2 as the result.

If your column A isn't in date order, so that you can't assume the date in one row is less than or equal to the date in the following row, then the best answer I can think of is to use the date functions to isolate the parts of the date you want to test, e.g.

"=IF(AND(YEAR($A1) = YEAR($A2), MONTH($A1) = MONTH($A2), DAY($A1) = DAY($A2)), $B1, $B1 + 1)"

Hope that helps.

This worked:

=IF(INT(A2) =INT(A3),B2,B2+1)

You might also consider:
=B1+(INT(A2)-INT(A1)>0)
or:
=B1+(INT(A2-A1)>0)
in B2 and copy down. With the second forumla, a difference of 24hrs will count as a 'day' difference, whereas your's treats them as different if only a 1-second difference results in a date change. As you'll see, no IF test is required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]