Time calculator minus time duration

[av1alicia]

I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments. With the final cell providing the unit number. With the unit rule of 8 applied
A1(time in)= 8:32a
A2(time out) = 10:40a
A3(calculates total time billed in hh:mm)
A4 (time billed divided by.25)= Units

The time between 8:32:00 AM and 10:40:00 AM is:

2 hours, 8 minutes,

Units = 8

 

[awoohaw]

I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments. With the final cell providing the unit number. With the unit rule of 8 applied
A1(time in)= 8:32a
A2(time out) = 10:40a
A3(calculates total time billed in hh:mm)
A4 (time billed divided by.25)= Units

The time between 8:32:00 AM and 10:40:00 AM is:

2 hours, 8 minutes,

Units = 8

So what is your question? What do you mean by "unit rule of 8"? Do you want in 15 second intervals or did you mean .25 of an hour? Will your time ever wrap around 00:00:00 (from 23:00:00 to 1:00:00 -next day)?

 

[awoohaw]

I'm guessing you want to round down minutes, by quarter hours. If you want to round down seconds by quarter seconds you can do that similarly:

Book1
	A	B	C	D
1	Time IN	Time OUT	Time Diff	units of 15 minutes each
2	08:32:00	10:55:00	02:23:00	9.00
Sheet4

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=(HOUR(B2-A2)/0.25)+(INT(MINUTE(B2-A2)/15))

 

[felixstraube]

Another option (formula in H2):

Book1
	A	B	C	D	E	F	G	H
1	In	Out	Time difference [h:mm]	Time difference in hours [h]	Units of 0.25 hours (quarter hour or 15 min)	Truncated Units	Rounded Units	All in one formula to get expeted result
2	8:32	10:40	2:08	2.13	8.533333333	8	9	8
Sheet2

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=C2*24
E2	E2	=D2/0.25
F2	F2	=TRUNC(E2,0)
G2	G2	=ROUND(E2,0)
H2	H2	=TRUNC((B2-A2)*24/0.25,0)

 

[av1alicia]

So what is your question? What do you mean by "unit rule of 8"? Do you want in 15 second intervals or did you mean .25 of an hour? Will your time ever wrap around 00:00:00 (from 23:00:00 to 1:00:00 -next day)?

No the time will never wrap to the next day

 

[av1alicia]

No the time will never go into the next day
And the time hh:mm then will convert into units billed every 15 minutes at one unit each

 

[av1alicia]

Yay! Yes these formulas work. Thank you Everyone. I will never be an excel master. I appreciate you that are!

 

[awoohaw]

Happy to help. And welcome to the forum.