Formula for elapsed working hours using a 12 hour clock

[Joycelyn Anderson]

I'm so glad I found this site!

Please help me with a formula for elapsed working hours using a 12 hour clock. I have read the previous threads pertaining to elapsed working time, and I am just not getting it to calculate correctly.

In the interest of full disclosure, my level of excel experience is remedial at best. (but i'm excited to learn!)

Thank you!

 

[Hui]

Jocelyn

Firstly, Welcome to the Chandoo.org Forums

Its simply =End Time - Start Time
A1: End Time 4:00 pm
A2: Start Time 8:00 am
A3: Elapsed Time =A1-A2
then format the cell as hh:mm

 

[Joycelyn Anderson]

Thank you!

Isn't it a bit more complicated than that for calculating working hours? If my scheduled hours are 7:00 am - 4:00 pm and I am given a task at 2:00 pm on Friday, then finish it at 9:30 am Monday morning; simply using the difference between end time and start time will include all the non-working hours over the weekend and calculate that the task took 67.5 hours, when actually it took 4 hours.

Perhaps the formulas I've seen here are working because I have a date and a time and they are in separate cells?

Example
A1: Start Date
B1: Start Time
C1: End Date
D1: End Time

Any solutions?

 

[Joycelyn Anderson]

correction: Perhaps the formulas I've seen are NOT working due to the time and date being in separate cells?

 

[Hui]

Can you please post a sample file?
But basically it is =(Closing date + Time)-(opening date + time)

 

[Joycelyn Anderson]

Thank you, the sample is attached. The formula(s) I need will account for the different scheduled working hours for each phase shown and show the total time as a number of hours. Also, you'll notice that in phase 3, May 30th is a Friday and June 2nd is a Monday. I need to exclude non-working hours from the totals.

In this example the correct formula will calculate 5.5 for G3, 6.5 for L3 and 4.5 for Q3.

(beyond that, the formula for the grand total is basic, so no worries with that.)

Thank you again! My head is near the point of exploding from trying to solve this

 

[Joycelyn Anderson]

One of my IT guys was able to help me. Here's what he came up with....

=MAX(0,(8*(NETWORKDAYS(C3,D3)-1)-24*((MOD(C3,1)-MOD(D3,1)))))

Also, I was initially incorrect in putting the dates in one cell and the time in another. Now that they are combined, excel is able to read it as one number. i.e. 6/2/2014 11:00 AM is 41792.4583333 rather than 6/2/2014 being 41792 and 11:00 AM being 0.4583333

Thanks so much for your help!