Calculate Time duration & day parts

**s023kaw** · 01-24-2011, 11:34 AM

I need some assistance with a excel formula. I have a spreadsheet that has the following columns (time in, time out, duration, morning day part, evening day part). First, I need to calculate the duration of time worked (e.g. 8am - 4pm would be 8 hours). The second part is then breaking that out into two different day parts (8am to 5pm & 5pm to midnight). So, if I worked from 10am to 4pm my duration would be 6 hours and I would have 6 hours in the morning day part column. If I worked 10am 7pm, I would have 9 hours of duration with 7 hours in the morning day part and 2 hours in the evening day part).

**DonkeyOte** · 01-24-2011, 12:09 PM

If we assume no shift crosses midnight (implied by windows) then with start in A2 and end in B2:

Please Login or Register  to view this content.

format C2:E2 as h:mm

given the time windows it might make more sense to simplify based on Evening, eg:

Please Login or Register  to view this content.

**s023kaw** · 01-24-2011, 12:59 PM

This works perfectly!! THANK YOU VERY MUCH

**s023kaw** · 01-25-2011, 10:27 AM

Now for the next challenge. How can I handle when a employee "clocks out" after midnight? Attached is a sample report. If you look at emp 4, when a clock out is after midnight, it calculates the evening hours correctly, but it then adds them to the morning part hours too.

Does anyone have any suggestions?

**DonkeyOte** · 01-26-2011, 06:33 AM

I did indirectly ask this question (crossing midnight) in my first post

Your windows do not appear to include time between midnight and 8am so it's hard to provide a definitive solution I'm afraid.

What I would say - from looking at your sample file - if as implied you have only 2 shifts to calculate then as per the original suggestions you need only calculate 1 of them in any detail.
The 2nd shift is simply the difference between total time worked and the other shift.

On something of an aside, for total hours you could use:

Please Login or Register  to view this content.

**DonkeyOte** · 01-26-2011, 08:40 AM

s023kaw, please note:

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web.
You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.
We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this

**kill_switch** · 03-13-2011, 08:41 AM

Hi Donkey. I'm sort of looking for the same outcome. However...

Question 1
For the crossing midnight part, couldn't we use an IF statement that if F5 is less than E5, then do that calculation.

But to implement that into the formula you provided earlier.

Please Login or Register  to view this content.

Question 2
For the total time. How would you make it display as "man hours". Because if someone worked for 2 hours and 23 minutes, I would need the Total hours displayed as "2.4" Hours based on the chart below.

////EDIT
For Question 2 I've had a moment after my 2nd coffee...and google'd correctly.

In J2

Please Login or Register  to view this content.

///END EDIT

Minutes, (Man hours)
01 (.02) 16 (.27) 31 (.52) 46 (.77)
02 (.03) 17 (.28) 32 (.53) 47 (.78)
03 (.05) 18 (.30) 33 (.55) 48 (.80)
04 (.07) 19 (.32) 34 (.57) 49 (.82)
05 (.08) 20 (.33) 35 (.58) 50 (.83)
06 (.10) 21 (.35) 36 (.60) 51 (.85)
07 (.12) 22 (.37) 37 (.62) 52 (.87)
08 (.13) 23 (.38) 38 (.63) 53 (.88)
09 (.15) 24 (.40) 39 (.65) 54 (.90)
10 (.17) 25 (.42) 40 (.67) 55 (.92)
11 (.18) 26 (.43) 41 (.68) 56 (.93)
12 (.20) 27 (.45) 42 (.70) 57 (.95)
13 (.22) 28 (.47) 43 (.72) 58 (.97)
14 (.23) 29 (.48) 44 (.73) 59 (.98)
15 (.25) 30 (.50) 45 (.75) 60 (1.00)

Thanks a lot. I've been scratching my head and losing sleep thinking about this last night.

**kill_switch** · 03-13-2011, 09:16 AM

Hi Donkey. I'm sort of looking for the same outcome. However...

Question 1
For the crossing midnight part, couldn't we use an IF statement that if F5 is less than E5, then do that calculation.

But to implement that into the formula you provided earlier.

Please Login or Register  to view this content.

Question 2
For the total time. How would you make it display as "man hours". Because if someone worked for 2 hours and 23 minutes, I would need the Total hours displayed as "2.4" Hours based on the chart below.

Minutes, (Man hours)
01 (.02) 16 (.27) 31 (.52) 46 (.77)
02 (.03) 17 (.28) 32 (.53) 47 (.78)
03 (.05) 18 (.30) 33 (.55) 48 (.80)
04 (.07) 19 (.32) 34 (.57) 49 (.82)
05 (.08) 20 (.33) 35 (.58) 50 (.83)
06 (.10) 21 (.35) 36 (.60) 51 (.85)
07 (.12) 22 (.37) 37 (.62) 52 (.87)
08 (.13) 23 (.38) 38 (.63) 53 (.88)
09 (.15) 24 (.40) 39 (.65) 54 (.90)
10 (.17) 25 (.42) 40 (.67) 55 (.92)
11 (.18) 26 (.43) 41 (.68) 56 (.93)
12 (.20) 27 (.45) 42 (.70) 57 (.95)
13 (.22) 28 (.47) 43 (.72) 58 (.97)
14 (.23) 29 (.48) 44 (.73) 59 (.98)
15 (.25) 30 (.50) 45 (.75) 60 (1.00)

Thanks a lot. I've been scratching my head and losing sleep thinking about this last night.

////
EDIT
////
For question 2 here is the formula to get the man hours. 2 cups of coffee did the trick. Was playing with the baby when I had the thought, and googled it. Not just gotta build my IF statement

//end//
=CEILING(I2*24, 0.1)