Calculating Time that goes over 12 am

[dkmusf]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday		Total Thursday	Friday		Total Friday	Saturday		Total Saturday	Sunday		Total Sunday	Monday	Tuesday	Wednesday		Weekly Total
2	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10:30 am - 10:00 pm	Closed	Closed	10:30 am - 10:00 pm	03:30 am	02:00 am	#VALUE!	03:30 am	12:00 am	#VALUE!	10:00 am	02:00 am	#VALUE!	10:30 pm	10:00 pm	#VALUE!	Closed	Closed	10:30 pm	10:00 pm	#VALUE!
3
4
Sheet1

Cell Formulas
Range	Formula
H2	H2	=REPLACE(A$2,7,10,"")
I2	I2	=REPLACE(A$2,1,10,"")
J2,S2,P2,M2	J2	=IF(I2="","",I2-H2)
K2	K2	=REPLACE(B$2,7,10,"")
L2	L2	=REPLACE(B$2,1,10,"")
N2	N2	=REPLACE(C$2,7,10,"")
O2	O2	=REPLACE(C$2,1,10,"")
Q2	Q2	=REPLACE(D$2,7,10,"")
R2	R2	=REPLACE(D$2,1,10,"")
V2	V2	=REPLACE(G$2,7,10,"")
W2	W2	=REPLACE(G$2,1,10,"")
X2	X2	=J2+M2+P2+S2

 

[PeteWright]

You could use VBA to get a custom function:

Public Function getTimeDiff(a As Date, e As Date) As Date
    'getTimeDiff= 24 * (TimeValue(e) - TimeValue(a))
    Dim tBeg#, tEnd#
    tBeg = TimeValue(a)
    tEnd = TimeValue(e)
    If tEnd < tBeg Then tEnd = tEnd + 1
    getTimeDiff = 24 * (tEnd - tBeg)
End Function

Book1.xlsm
	A	B	C
1	start	end	hours
2	10:00:00 PM	6:00:00 AM	8.00
Sheet33

Cell Formulas
Range	Formula
C2	C2	= getTimeDiff(A2,B2)

 

[awoohaw]

My approach to this may be different from many others. But, I always try to use a date and time value and format only the time portion.

mr excel questions 20.xlsm
	A	B	C
1			Difference
2	2023-02-24 22:15	2023-02-25 02:30	04:15
3	44981.92708	44982.10417	0.177083333
4	22:15	02:30	04:15
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=B2-A2
A3:B4	A3	=A2

 

[Dave Patton]

try

T202303a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday		Total Thursday
2	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10:30 am - 10:00 pm	Closed	Closed	10:30 am - 10:00 pm	3:30 PM	2:00 AM	10:30
3
5f

Cell Formulas
Range	Formula
H2	H2	=--TEXTBEFORE(A2,"-")
I2	I2	=--TEXTAFTER(A2,"- ")
J2	J2	=IF(I2="","",I2-H2+(H2>I2))

 

[PeteWright]

If you don't like VBA use this:

Book1.xlsm
	A	B	C
1	start	end	hours
2	10:00:00 PM	6:00:00 AM	8.00
3	10:00:00 PM	6:00:00 AM	8.00
Sheet33

Cell Formulas
Range	Formula
C2	C2	= getTimeDiff(A2,B2)
C3	C3	= IF(B3<A3,24*(B3+1-A3),24*(B3-A3))

 

[AhoyNC]

Another option is the MOD function:

Book1
	A	B	C	D
1	Thursday	Thursday		Total Thursday
2	03:30 pm - 02:00 am	3:30 PM	2:00 AM	10:30
3		1:00 PM	5:45 PM	4:45
Sheet2

Cell Formulas
Range	Formula
B2	B2	=--TEXTBEFORE(A2,"-")
C2	C2	=--TEXTAFTER(A2,"- ")
D2:D3	D2	=IF(C2="","",MOD(C2-B2,1))

 

[Dave Patton]

T202303a.xlsm
	A	B	C	D	E	F
6	Thursday	Friday	Saturday	Thursday	Friday	Saturday
7	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10:30	8:30	16:00
8	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10.5	8.5	16
9
5f

Cell Formulas
Range	Formula
D7:F7	D7	=IF(A7="","",TEXTAFTER(A7,"- ")-TEXTBEFORE(A7,"-")+(TEXTBEFORE(A7,"- ")>TEXTAFTER(A7,"-")))
D8:F8	D8	=IF(A8="","",TEXTAFTER(A8,"- ")-TEXTBEFORE(A8,"-")+(TEXTBEFORE(A8,"- ")>TEXTAFTER(A8,"-")))*24

 

[Dave Patton]

T202303a.xlsm
	A	B	C	D	E	F	G
6	Thursday	Friday	Saturday	Thursday	Friday	Saturday
7	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10:30	8:30	16:00	or
8	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10.5	8.5	16
5f

Cell Formulas
Range	Formula
D7:F7	D7	=LET(s,TEXTBEFORE(A7,"-"),e,TEXTAFTER(A7,"- "),e-s+(s+0>e+0))
D8:F8	D8	=LET(s,TEXTBEFORE(A8,"-"),e,TEXTAFTER(A8,"- "),e-s+(s+0>e+0))*24

 

[dkmusf]

Thank you, everyone. I will need to unpack all of this later this evening!!!

 

[Dave Patton]

T202303a.xlsm
	A	B	C	D	E	F
9	03:30 pm - 02:00 am	03:30 pm - 12:00 am	10:00 am - 02:00 am	10.5	8.5	16
10
5f

Cell Formulas
Range	Formula
D9:F9	D9	=LET(s,TEXTBEFORE(A9,"-"),e,TEXTAFTER(A9,"- "),MOD(e-s,1)*24)