Count consecutive dates

[cristinutika]

Hello Everyone,

I would like to ask for your support regarding how can I count any consecutive dates for each employee based on Unique report number and Destination.
Basically, I want to count how many consecutive dates a employee stayed in a same location.
Is this possible to do using an excel formula?

Thank you in advance!

 

[jasonb75]

Assuming that your dates are valid, try this formula in E2 and fill down

=IF(AND(A3=A2,B3=B2,C3=C2,D3=(D2+1)),"",ROW(E2)-IFERROR(MATCH(1e+100,D$1:D1),1))

If you see #VALUE! then that would mean that your dates are not in a valid format, in which case, try using find and replace (ctrl h) to change . to / in all of the dates.

 

[cristinutika]

=IF(AND(A3=A2,B3=B2,C3=C2,D3=(D2+1)),"",ROW(E2)-IFERROR(MATCH(1e+100,D$1:D1),1))

Thank you for your support! It seems that something is wrong here...

 

[cristinutika]

Thank you for your support! It seems that something is wrong here...

 

[jasonb75]

Try changing the commas to semicolons.

 

[cristinutika]

Try changing the commas to semicolons.

The first 14 dates it counted correctly but not the next ones... as the next example is related to another employee as it should be count from the beginning. Thank you in advance!

 

[jasonb75]

Why did you change 1e+100 to E1+100?

That is why it is not working.

 

[cristinutika]

Why did you change 1e+100 to E1+100?

That is why it is not working.

Sorry, I thought it's a mistake. Now it looks better but it does not count how many consecutive days I have (to total). Thanks!!!

 

[jasonb75]

Sorry, that was a mistake that I missed. Try this one instead, hopefully I have it correct for your settings.

=IF(AND(A3=A2;B3=B2;C3=C2;D3=(D2+1));"";ROW(E2)-IFERROR(MATCH(1e+100;E$1:E1);1))

For future reference, it is preferable if you use XL2BB (green button on the reply toolbar) to post examples instead of screen captures, that way we can copy and paste it into excel and test any formulas before posting them, eliminating most errors.

A formula will not work on a screen capture, so more often than not they are posted untested.

 

[cristinutika]

Sorry, that was a mistake that I missed. Try this one instead, hopefully I have it correct for your settings.

=IF(AND(A3=A2;B3=B2;C3=C2;D3=(D2+1));"";ROW(E2)-IFERROR(MATCH(1e+100;E$1:E1);1))

For future reference, it is preferable if you use XL2BB (green button on the reply toolbar) to post examples instead of screen captures, that way we can copy and paste it into excel and test any formulas before posting them, eliminating most errors.

A formula will not work on a screen capture, so more often than not they are posted untested.

It works :D Yuhuu! Thanks a lot for your support!