Finding range between start times, spanning across midnight

Hi,

I have various start times for employees.

I need to find out the total movement in start times per week for the employees.

I have used MIN and MAX to find the earliest and latest start times, and then a subtraction to find the difference.

This works brilliantly until my employees who start around midnight.

If they have started before and after midnight, it takes midnight as the earliest time and 23:00 as the latest, giving a difference of 23 hours instead of 1 hour.

i.e.

Start Times
23:00, 23:15, 00:15, 00:30

Range of Movement
00:30 - 23:00 = 01:30

I've tried, =$B$9+($C$4>$B$9)-$C$4 but this does not work when the MIN reads 00:00.

Any ideas guys?

kashflo

Hi
You need to include the actual date in your source cells and it will then give you the correct time difference.
Hope this helps.
Tony

Tony,

Thanks for your response.

We can't include the date, because the start times are manually keyed in to a spreadsheet.

kashflo

By range of movement do you mean the number of hours between 2 times. If it is the case try the following :

Please Login or Register  to view this content.

It will take care the midnight span ... if you want it in terms of number of hours multiply the above by 24 or utilize the

Please Login or Register  to view this content.

function:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope that helps