Text String to time

[Mark McInerney]

Hi,

I'm working with a range of cells where the client has entered times as text strings and all into the one cell e.g. 9.00-14.00, 15.00-21.00

They are working on a 24 hour time with 2pm entered as 14.00

is it possible to extract the start time and the end time and covert them into times that I can use in other formulas?

Thanks for any help - appreciated - Mark.

 

[Dave Patton]

Time 2023.xlsm
	D	E	F	G	H	I	J	K
3					#6	#7	#7 without MOD	#8
4	9.00-14.00				5:00	5:00	5:00	5:00
5	15.00-21.00				6:00	6:00	6:00	6:00
6	6.08-10.59				4:51	4:51	4:51	4:51
7	9.55-23.01				13:06	13:06	13:06	13:06
8	18.15-6.00	time spans midnight			11:45	11:45	11:45	11:45
9
2g

Cell Formulas
Range	Formula
H4:H8	H4	=LET(t,--TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-"),INDEX(t,2)-INDEX(t,1)+(INDEX(t,1)>INDEX(t,2)))
I4:I8	I4	=LET(t,TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))
J4:J8	J4	=LET(t,TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))
K4:K8	K4	=SUM(MOD(TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-")*{-1,1},1))

 

[Peter_SSs]

If the time spans midnight, our suggestions calculate the correct time.

Fair enough, I had missed that. However, the thread has diverted a fair way from the original question and data so I'm leaving it alone now unless we hear more from the OP.

 

[Scott Huish]

Wouldn't that do the same thing without the MOD()?

I always include it in case times cross midnight.