Convert Text Times for calculations

[bigroo1958]

Hello,

Our system throws out start and Stop times as text, I want to perform calculations, how can I convert in excel?

So, Start Time is in this format =" 9:10" which means 9:10am
and Stop Time is in this format =" 2:00" which means 2:00pm

I want this format 00:00:00 Hrs/Mins/Secs so I can calculate the elapsed time.

I have tried reformat the cell with little luck.

Thanks

[JeteMc]

If " 9:10" is 9:10 am and " 2:00" is 2:00 pm then what are 9:00 pm and 2:00 am?

[Ron Coderre]

With your sample values in A1 and B1

This formula returns the time value of A1
C1: =--TRIM(A1)

and this formula returns the time value of B1.
Note: because the values don't indicate if they're AM or PM, if the B1 time is prior to C1, it adds 12 hours.
D1: =--TRIM(B1)+IF(--TRIM(B1)<C1,TIME(12,,),0)

Format C1:D1 as time.




Is that something you can work with?

[bigroo1958]

Hi Ron Coderre,

Yes, this works out nicely indeed!!
Thanks

[bigroo1958]

Hi Ron Coderre,

I think I jumped the gun with this, please review the attachment, I see an issue with the elapsed times.
All sensitive information has been removed.

Please advise how to correct this, thanks

[Oppressed1]

You still need to address JeteMC's AM/PM concern, but try
=TIMEVALUE(A1)
and optionally format that as time. That works for a cell with exact formula
A1=" 9:10"
as well as a copy of that by value.

Formatting is actually not needed for elapsed time calculation; the date values are subtractable either way. However I understand that you want it shown on the result of elapsed time, so you can format that as 0:00:00.

If A2=" 9:20" then timevalue(a2)-timevalue(a1) gives 0:10:00

Note you can go 86400*(timevalue(a2)-timevalue(a1)) for total number of seconds, handy in some time subtraction (elapsed) applications.

Special consideration is called for if the span of your start and stop times may cross midnight.

BTW I did not look at your attachment.

[Ron Coderre]

Thank you for the example.
The formulas in Col_AD should be checking if the converted Col_H time is less than the converted Col_G time....in Col_AC, not Col_G

Use this formula and copy it down as far as you need

Please Login or Register  to view this content.

Not this:

Please Login or Register  to view this content.

Does that help?

[bigroo1958]

Thanks Ron, Wow I did not see that mistake on my behalf, works nicely now.
A simple typo!!

Thanks

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)