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.

 

[etaf]

can you give some examples of how the dates are entered , all possible formats , you say 14:00 and also 2pm
do they always use a -
if so then textbefore and text after
=TIMEVALUE(TEXTBEFORE(A2,"-"))
=TIMEVALUE(TEXTAFTER(A2,"-"))

If its has PM or AM - then need substitute to add a space after the number and AM / PM
if a mixture of both , i'm not sure the best way - i have quite a complicated substitution
=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTBEFORE(A3,"-"))),SUBSTITUTE(UPPER(TEXTBEFORE(A3,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTBEFORE(A3,"-")),"PM"," PM")))
=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTAFTER(A3,"-"))),SUBSTITUTE(UPPER(TEXTAFTER(A3,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTAFTER(A3,"-")),"PM"," PM")))

Book16
	A	B	C	D	E	F
1					AM time	PM time
2	09:00-14:00	0.375	0.58333333		0.375	0.58333333
3	9am-2pm	0.375	0.58333333		0.375	0.58333333
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TIMEVALUE(TEXTBEFORE(A2,"-"))
C2	C2	=TIMEVALUE(TEXTAFTER(A2,"-"))
B3	B3	=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTBEFORE(A3,"-"))),SUBSTITUTE(UPPER(TEXTBEFORE(A3,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTBEFORE(A3,"-")),"PM"," PM")))
C3	C3	=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTAFTER(A3,"-"))),SUBSTITUTE(UPPER(TEXTAFTER(A3,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTAFTER(A3,"-")),"PM"," PM")))
E2:E3	E2	=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTBEFORE(A2,"-"))),SUBSTITUTE(UPPER(TEXTBEFORE(A2,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTBEFORE(A2,"-")),"PM"," PM")))
F2:F3	F2	=TIMEVALUE(IF(ISNUMBER(SEARCH("AM",TEXTAFTER(A2,"-"))),SUBSTITUTE(UPPER(TEXTAFTER(A2,"-")),"AM"," AM"),SUBSTITUTE(UPPER(TEXTAFTER(A2,"-")),"PM"," PM")))

 

[Mark McInerney]

Hi etaf,

Thanks for taking a look - I have been playing around with what you sent me, and this formula appears to be getting me exactly what I need.

=(TIMEVALUE(REPLACE(TEXTAFTER(D6,"-"),FIND(".",TEXTAFTER(D6,"-")),1,":")

Thank you for your help - greatly appreciated.

 

[Peter_SSs]

.. this formula appears to be getting me exactly what I need.

=(TIMEVALUE(REPLACE(TEXTAFTER(D6,"-"),FIND(".",TEXTAFTER(D6,"-")),1,":")

That isn't a valid formula. In any case it, even if you add the extra parentheses to make it one, it only extracts the end time when you asked for both.

Perhaps you could consider this fairly short one that extracts both times?

24 01 28.xlsm
	D	E	F
5		Start	End
6	9.00-14.00	09:00	14:00
7	15.00-21.00	15:00	21:00
8	6.08-10.59	06:08	10:59
9	9.55-23.01	09:55	23:01
Times

Cell Formulas
Range	Formula
E6:F9	E6	=--TEXTSPLIT(SUBSTITUTE(D6,".",":"),"-")
Dynamic array formulas.

 

[Alex Blakenburg]

If you have any intention of using an Excel Table, TextBefore and TextAfter is a safer bet.
TextSplit will give you a SPILL error.

Book1
	A	B	C
1	Text	Start	End
2	9.00-14.00	9:00	14:00
3	15.00-21.00	15:00	21:00
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=--SUBSTITUTE(TEXTBEFORE([@Text],"-"),".",":")
C2:C3	C2	=--SUBSTITUTE(TEXTAFTER([@Text],"-"),".",":")

 

[Dave Patton]

If your goal is to calculate the time worked, see the following.
Building on Peter's suggestion see below or use the same logic with Alex's suggestion.

Time 2023.xlsm
	D	E
2		Time
3	9.00-14.00	5:00
4	15.00-21.00	6:00
5	6.08-10.59	4:51
6	9.55-23.01	13:06
7	18.15-6.00	11:45
2g

Cell Formulas
Range	Formula
E3:E7	E3	=LET(t,--TEXTSPLIT(SUBSTITUTE(D3,".",":"),"-"),INDEX(t,2)-INDEX(t,1)+(INDEX(t,1)>INDEX(t,2)))

 

[Scott Huish]

If your goal is to calculate the time worked, see the following.
Building on Peter's suggestion see below or use the same logic with Alex's suggestion.

Time 2023.xlsm
	D	E
2		Time
3	9.00-14.00	5:00
4	15.00-21.00	6:00
5	6.08-10.59	4:51
6	9.55-23.01	13:06
7	18.15-6.00	11:45
2g

Cell Formulas
Range	Formula
E3:E7	E3	=LET(t,--TEXTSPLIT(SUBSTITUTE(D3,".",":"),"-"),INDEX(t,2)-INDEX(t,1)+(INDEX(t,1)>INDEX(t,2)))

A bit shorter:
=LET(t,TEXTSPLIT(SUBSTITUTE(D3,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))

 

[Peter_SSs]

A bit shorter:
=LET(t,TEXTSPLIT(SUBSTITUTE(D3,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))

Wouldn't that do the same thing without the MOD()?
In any case, another shorter one.
(Remembering of course that these are not what the OP actually asked for)

24 01 28.xlsm
	D				H	I	J	K
4					Post
5					#6	#7	#7 without MOD	#8
6	9.00-14.00				05:00	05:00	05:00	05:00
7	15.00-21.00				06:00	06:00	06:00	06:00
8	6.08-10.59				04:51	04:51	04:51	04:51
9	9.55-23.01				13:06	13:06	13:06	13:06
Times (3)

Cell Formulas
Range	Formula
H6:H9	H6	=LET(t,--TEXTSPLIT(SUBSTITUTE(D6,".",":"),"-"),INDEX(t,2)-INDEX(t,1)+(INDEX(t,1)>INDEX(t,2)))
I6:I9	I6	=LET(t,TEXTSPLIT(SUBSTITUTE(D6,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))
J6:J9	J6	=LET(t,TEXTSPLIT(SUBSTITUTE(D6,".",":"),"-"),INDEX(t,2)-INDEX(t,1))
K6:K9	K6	=SUM(TEXTSPLIT(SUBSTITUTE(D6,".",":"),"-")*{-1,1})

 

[Dave Patton]

If the time spans midnight, our suggestions calculate the correct time. This was shown in posts 6 and 7

Time 2023.xlsm
	D	E	F	G	H	I	J	K
2					Post
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	###########	########
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,".",":"),"-"),INDEX(t,2)-INDEX(t,1))
K4:K8	K4	=SUM(TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-")*{-1,1})

 

[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
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:J7	J4	=LET(t,TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-"),INDEX(t,2)-INDEX(t,1))
K4:K8	K4	=SUM(MOD(TEXTSPLIT(SUBSTITUTE(D4,".",":"),"-")*{-1,1},1))
J8	J8	=LET(t,TEXTSPLIT(SUBSTITUTE(D8,".",":"),"-"),MOD(INDEX(t,2)-INDEX(t,1),1))