Calculate time differences

[wtom0412]

Hello All,

I am stuck. I need to design a workbook that charges customers based on staff time sheets.

This is a reduced version I am using to work out the formulas before I transfer it into the proper sheet...

So, if the hours worked are between 6am and 6pm, they are charged at Rate 1, if the hours are after 6pm and before 6am, they are charged at Rate 2.

I have worked out how to calculate the number of hours for Rate 2 (Formula in C3)

=IF(A3<A1,(A1-A3)*24,0)+IF(B3>B1,(B3-B1)*24,0)

, but I am having specific difficulty working out how to calculate the hours BETWEEN 6am and 6pm when the hours start before 6am and / or after 6pm.

I hope this all makes sense and you can help me?

Thank you, Toby.

 

[jasonb75]

Taking a different approach, this will do what you need. Hopefully it will make sense but please ask if you need anything explaining.

Book1
	A	B	C	D	E
2			6pm - 6am	6am - 6pm	Total Hours
3	7:00 AM	8:00 PM	2.00	11.00	13.00
4	8:00 PM	7:00 AM	11.00	0.00	11.00
5	8:00 PM	5:00 AM	9.00	0.00	9.00
6	8:00 AM	2:00 PM	0.00	6.00	6.00
Sheet3

Cell Formulas
Range	Formula
C3:C6	C3	=E3-D3
D3:D6	D3	=MOD(MEDIAN(B3,0.25+(A3>B3),0.75)-MEDIAN(A3,0.25,0.75),1)*24
E3:E6	E3	=MOD(B3-A3,1)*24

 

[wtom0412]

T

Taking a different approach, this will do what you need. Hopefully it will make sense but please ask if you need anything explaining.

Book1
	A	B	C	D	E
2			6pm - 6am	6am - 6pm	Total Hours
3	7:00 AM	8:00 PM	2.00	11.00	13.00
4	8:00 PM	7:00 AM	11.00	0.00	11.00
5	8:00 PM	5:00 AM	9.00	0.00	9.00
6	8:00 AM	2:00 PM	0.00	6.00	6.00
Sheet3

Cell Formulas
Range	Formula
C3:C6	C3	=E3-D3
D3:D6	D3	=MOD(MEDIAN(B3,0.25+(A3>B3),0.75)-MEDIAN(A3,0.25,0.75),1)*24
E3:E6	E3	=MOD(B3-A3,1)*24

Thank you so much, it works exactly like I need it to, I am so appreciative of your time and expertise.

I am not sure I underatnd all of the formulas, but if I may (when I have more time tomorrow), may i study it and ask you any questions?

Cheers, Toby

 

[wtom0412]

T

Thank you so much, it works exactly like I need it to, I am so appreciative of your time and expertise.

I am not sure I underatnd all of the formulas, but if I may (when I have more time tomorrow), may i study it and ask you any questions?

Cheers, Toby

Hi Jason,

I think there is a bug? When the times are within the AM to PM band, the calculation results in Zero, which means there is nothing to base the dollar calculation on? I think because the formula in C2 takes the hours worked within that band off the Total Hours worked - if they are the same, the result is zero.

The PM to AM band works correctly.

Thanks again for your help.

Cheers, Toby

 

[jasonb75]

It took me a while to figure out where the incorrect result was, I double checked my test sheet several times before finding the problem.

This version looks correct and should get you going. I'll have another look at it at some point because I think that the 6am-6pm formula should be shorter, need to give it a few days to try and forget all of the 'wrong ideas' that are stuck in my mind at the moment before trying again though.

Book1
	A	B	C	D	E
2			6pm - 6am	6am - 6pm	Total Hours
3	07:00:00	20:00:00	2.00	11.00	13.00
4	20:00:00	07:00:00	10.00	1.00	11.00
5	20:00:00	05:00:00	9.00	0.00	9.00
6	08:00:00	14:00:00	0.00	6.00	6.00
Sheet2

Cell Formulas
Range	Formula
C3:C6	C3	=E3-D3
D3:D6	D3	=MOD(IF(A3>B3,MOD(B3-A3,1)-(MEDIAN(B3+1,1.25,0.75)-MEDIAN(A3,1.25,0.75)),MEDIAN(B3,0.25,0.75)-MEDIAN(A3,0.25,0.75)),1)*24
E3:E6	E3	=MOD(B3-A3,1)*24