Calculating time format

[bosstec]

I have some small problem with time formulas. I need to calculate how many hours between start and stop of working time. Including extended hours (night hours).
Please see my example. The link is directed to my homepage where the file can be downloaded.

http://www.dasfoto.dk/mrexcel

Thanks

Reg Frank

 

[Jerry Sullivan]

Hello Frank,

I believe the formulas shown below should work.
The formulas use named ranges for the Normal Start/Stop to make the formula easier to follow.
It also allows you to change your Normal Hours without changing the formulas.

Excel Workbook
	A	B	C	D	E	F	G
1	Start	Stop	Total hours	Normal hours	Extended hours
2	07:00	17:00	10	10	0
3	4:00	15:00	11	9	2
4	12:00	23:30	11.5	11	0.5
5	12:00	2:00	14	11	3
6	4:30	23:15	18.75	17	1.75
7	4:30	0:30	20	17	3
8							Roules:
9	nStart	nStop					Normal hour is between 06:00 - 23:00
10	6:00	23:00					Extended hour (night) is between: 23:00 - 06:00
11
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

The formulas are built on the assumption that the difference between Start and Stop time on any row does not exceed 24 hours.

 

[bosstec]

Thank you, Thank you, Thank you...

It is woorking..!

Can you explain why the formula in D2 is working

Reg Frank

 

[bosstec]

Ups I found a problem...

If start time is 23:30 and stop time is 08:00 then the result should be:

Total hours: 8,5 (ok)
Normal hours: 2 (showing 0)
Extended hours: 6,5 (showing 8,5)

Can anyone help with this?

Thanks
Reg Frank

 

[Jerry Sullivan]

Ups I found a problem...

If start time is 23:30 and stop time is 08:00 then the result should be:

Total hours: 8,5 (ok)
Normal hours: 2 (showing 0)
Extended hours: 6,5 (showing 8,5)

Oh, you want it to work for any combination of times entered not just the ones in your example? That's harder..

I think (hope) the revised formula below will do that.

You'll see a few other tricky combinations that wouldn't have worked with my first formula.

Excel Workbook
	A	B	C	D	E	F	G
1	Start	Stop	Total hours	Normal hours	Extended hours
2	07:00	17:00	10	10	0
3	4:00	15:00	11	9	2
4	12:00	23:30	11.5	11	0.5
5	12:00	2:00	14	11	3
6	4:30	23:15	18.75	17	1.75
7	4:30	0:30	20	17	3
8	23:30	8:00	8.5	2	6.5
9	23:45	23:30	23.75	17	6.75
10	23:45	0:00	0.25	0	0.25
11	12:00	11:00	23	16	7
12	0:00	23:45	23.75	17	6.75
13							Roules:
14	nStart	nStop					Normal hour is between 06:00 - 23:00
15	6:00	23:00					Extended hour (night) is between: 23:00 - 06:00
16
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[bosstec]

Thanks Jerry

It works very well now.

Reg Frank