Calculating times

[dpaton05]

I am trying to make a spreadsheet that calculates wages between a start time and the end of the day, so 12am. There is a set wage cost/hr from 6am - 6pm and another wage cost/hr from 6pm - 6am.

• The start time is in H19
• The cost/hr for 6am - 6pm is in I41
• The cost/hr for 6pm - 6am is in I42

Can someone help me please with a formula that will calculate the wage cost for the portion of the day?

 

[lrobbo314]

Here is a way using a lookup table.

Book1
	A	B	C	D	E	F
1	Start Time	End Time	Total		Time	Value
2	3:00 PM	8:00 PM	$75.00		12:00 AM	15
3					1:00 AM	15
4					2:00 AM	15
5					3:00 AM	15
6					4:00 AM	15
7					5:00 AM	15
8					6:00 AM	10
9					7:00 AM	10
10					8:00 AM	10
11					9:00 AM	10
12					10:00 AM	10
13					11:00 AM	10
14					12:00 PM	10
15					1:00 PM	10
16					2:00 PM	10
17					3:00 PM	10
18					4:00 PM	10
19					5:00 PM	10
20					6:00 PM	15
21					7:00 PM	15
22					8:00 PM	15
23					9:00 PM	15
24					10:00 PM	15
25					11:00 PM	15
Sheet5

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(LOOKUP((ROW(INDIRECT(HOUR(A2) & ":" & HOUR(B2)))*(1/24)),$E$2:$E$25,$F$2:$F$25))
E3:E25	E3	=E2+(1/24)

 

[dpaton05]

Thanks for that, what if the start time is not exactly at the hour, but say 15:15:00?

 

[dpaton05]

I tried the formula and it gave me a #REF error, this is what I had,
=SUMPRODUCT(LOOKUP((ROW(INDIRECT(HOUR(H46) & ":" & HOUR(H47)))*(1/24)),$H$51:$H$74,$I$51:$I$74))

• Start time is in H46
• End time is in H47 (seeing as though I want to find the wage cost for that portion of the day from the start time till 12am, I made the value in H47 0:00:00)
• The times are in H51 to H74
• The relevant hourly wages are in I51 to I74