Sorting with Dates!

Thank you for your response!

The spaces between the rows contained personal information about the employee position I deleted it, sorry I should have put something in there! The employee’s work 24 hour shifts the days they work are irrelevant as they operate 24-7-365.

Originally Posted by Special-K

Isn't this simply a case of:

Given a date in column E if it's 1 more than it's preceding date and the equivalent hours in column Q are both 24 then employee has worked two 24 hrs shifts on consecutive days.

Ok, I'm not sure why you have a space between all the rows except row 12 and 13, maybe youve deliberately entered data to provide an example.

If there is always a space between dates then

From R6 downwards enter

=IF(AND(E6=E4+1,Q6=24,Q4=24),1,0)

Column R will show a 1 where they have worked double shift on consecutive days else it will show 0.

If there may or may not be a space between rows you need to delete the rows that are empty and amend E4 and Q4 in the formula above to E5 and Q5 respectively.

What happens when an employee does a 24 shift on Friday and the same on Monday?
This solution only picks up consecutive days not consecutive working days.

Sorry I misunderstood. The reason there’s a row between the rows is because the example is an output from a staffing program I’ve no control over that. The program won’t tell me what I need to know so I’m thinking I could dump a report from it and use excel to sort it.

I don’t appear to be able to get:

=IF(AND(E6=E4+1,Q6=24,Q4=24),1,0)

To do anything for me, everything returns as 0 even though it should be showing a 1 from row 12 and 13?

Anyone have any idea's?

Ok I'm lost here.. I can't get that formula to show a 1 everything is zero and it complains about the cells not being the same size? Is there any way I can do this without having to change the example too much. The example is an export from a not so great staffing system.

I'd like to get a tally on how many times each person has pulled a double shift.. two 24 hour shifts side by side. As the everyone can work 24-7-365 weekends and holidays don't matter.

I'm still not getting it? I can't sort it by date either or else this might be easy! It's talking about merged cells being the same size! Any suggestions on making this thing would would be most welcome.

Originally Posted by shg

Don't merge cells.

I didn't.. the great "staffing system" spits out it's reports in excel like that. If unmerged the data gets spread out.