Elapsed Time

[joja]

A problem that's driving me nuts....I have a start date and time and an end date and time (this format dd/mm/yyyy hh:mm) for specific activities. In essence I need to do the foloowing and can't make it work !

Step 1)
I need to calculate the number of elapsed minutes between the start date and the end date

Step 2)
I then need to be able to split the elapsed time between the minutes that elapsed during opening hours (Mon - Fri 08:00 until 18:00) and those minutes that fell outside of working hours.....

Help........

[ChemistB]

Step 1. Excel looks at days as a single unit of measure, i.e. 2/13/2011 + 1 = 2/14/2011. To convert to minutes, multiply by 24 * 60 or 1440 and format as General.

how long are your time intervals? Is this like an employee timesheet and some of the times are 3rd shift (over midnight)? Or are we talking about projects and the times can span days?

[joja]

Thank you :O) Step 1 works perfectly now and makes so much sense now someone has pointed it out !

Step 2 - It'll sometimes span days between the start and end

[daddylonglegs]

If the start date/time and end date time will always be within opening hours you can use this formula to calculate time within opening hours

=(NETWORKDAYS(A2,B2)-1)*("18:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

where A2 is start date/time and b2 end date/time

format result cell as [h]:mm to get a result like 8:30


If you want that in minutes (510) then use

=((NETWORKDAYS(A2,B2)-1)*("18:00"-"8:00")+MOD(B2,1)-MOD(A2,1))*1440

format result cell as general