Separating Time using Formula

[jei eusebio]

Hi guys! Need help on how I can get the Start & End time from the data that I download from a client site. For example, 18:00-19:00 where 18:00 is the Start Time & 19:00 is the End Time. I tried to use =Left but it won't work with formulas when we try to subtract the minutes with other data. Not really good with formulas, most of the things I use are from Chandorg .. ^_^ lol .. appreciate the help! thanks in advance!

 

[Somendra Misra]

Hi,

Assuming time in A1, try below formula for start time.

=TIMEVALUE(LEFT(A1,FIND("-",A1)-1))

and below formula for end time:

=TIMEVALUE(MID(A1,FIND("-",A1)+1,255))

Format both formula cell as TIME.

Regards,

 

[jei eusebio]

It worked! Thanks a lot Somendra! :D

 

[Somendra Misra]

You are Welcome!!!.

Regards,

 

[Khalid NGO]

Hi,

Just adding another way, as you've mentioned you tried =LEFT( but not worked; it will work, you just need to convert it to a number by adding +0 at end.

for example
start time:
=LEFT(A1,5)+0

end time:
=RIGHT(A1,5)+0

Format both as time.

Regards,

 

[jei eusebio]

Thanks for the tip Khalid! :D

 

[Khalid NGO]

Thanks for the tip Khalid! :D

You are welcome.