Formula for consecutive days worked

[stryped]

I need a formula showing how many consecutive days worked, based on a date in column "E". It should only count the day as worked if column "L" for that date is blank.



Any help is appreciated!consecutive days example.xlsx

[6StringJazzer]

It would be really helpful to show an example of what result you want, since what you described in your post could be interpreted in more than one way.

"Consecutive days" would normally be "consecutive days ending on some date". So I added a column showing for each day, the number of consecutive days works up until that day.

If that's not what you want please show an example of the result

[stryped]

I found a mistake. It should count the number of consecutive days worked unless something is in column "L", On the example, it shows correctly 8 consecutive days as of 8/5/22. However, the next date is 8/8/22 (which is not consecutive.) So It should start over at 1. Instead, on this example it counts that date.

Attachment says:

Wed 08/03/2022 06:51 AM - 03:30 PM 8.00 002191 6
Thu 08/04/2022 06:47 AM - 03:30 PM 8.00 002191 7
Fri 08/05/2022 06:52 AM - 03:30 PM 8.00 002191 8
Mon 08/08/2022 06:51 AM - 03:30 PM 8.00 002191 9
Tue 08/09/2022 06:51 AM - 03:30 PM 8.00 002191 10
Wed 08/10/2022 06:54 AM - 03:31 PM 8.00 002191 11

Should be:

Wed 08/03/2022 06:51 AM - 03:30 PM 8.00 002191 6
Thu 08/04/2022 06:47 AM - 03:30 PM 8.00 002191 7
Fri 08/05/2022 06:52 AM - 03:30 PM 8.00 002191 8
Mon 08/08/2022 06:51 AM - 03:30 PM 8.00 002191 1
Tue 08/09/2022 06:51 AM - 03:30 PM 8.00 002191 2
Wed 08/10/2022 06:54 AM - 03:31 PM 8.00 002191 3

[6StringJazzer]

That's why I said you need to show sample results.

So you want a count of consecutive calendar days, regardless of whether a day is in your list. So if a date is missing from your list, it automatically resets the count?

To do that you have to convert the text in column E to real dates. See attached.