Forum Discussion
How to convert my unique time format to excel time format
This formula requires 365.
= LET( pm, IF(RIGHT(timeInput,1)="p", 0.5, 0), n, LEN(timeInput), h, IF(n<4, LEFT(timeInput, n-1), LEFT(timeInput, n-3)), m, IF(n<4, 0, MID(timeInput, n-2, 2)), time, TIME(h, m, 0) + pm, start, TAKE(time,,1), end, TAKE(time,,-1), duration, MOD(end - start, 1), HSTACK(time, duration) )and returns time as a fraction of a day but formatted as "[h]:mm".
This formula requires 365.
= LET(
pm, IF(RIGHT(timeInput,1)="p", 0.5, 0),
n, LEN(timeInput),
h, IF(n<4, LEFT(timeInput, n-1), LEFT(timeInput, n-3)),
m, IF(n<4, 0, MID(timeInput, n-2, 2)),
time, TIME(h, m, 0) + pm,
start, TAKE(time,,1),
end, TAKE(time,,-1),
duration, MOD(end - start, 1),
HSTACK(time, duration)
)and returns time as a fraction of a day but formatted as "[h]:mm".
- SnowMan55 has kindly pointed out that the formula I posted does not evaluate correctly for the first hour of the morning or afternoon. He proposed a second half-day adjustment for h=12.
= LET( timeinput, A2:B14, adj_for_pm, IF(RIGHT(timeinput,1)="p", 0.5, 0), n, LEN(timeinput), h, VALUE( IF(n<4, LEFT(timeinput, n-1), LEFT(timeinput, n-3)) ), m, VALUE( IF(n<4, 0, MID(timeinput, n-2, 2)) ), adj_for_hr12, IF(h=12, -0.5, 0), TIME(h, m, 0) + adj_for_pm + adj_for_hr12 )The 'adj_for_hr12' conditionally negates 'adj_for_pm', preventing miscalculation for times before 1:00 AM, and preventing overflow to the more than 1 day for times from noon until just before 1:00 PM.
Thanks to SnowMan55 identifying the problem, I was able to come up with the possible solution
=IF( ISNUMBER(timeInput), timeInput, LET( pm, IF(RIGHT(timeInput, 1) = "p", 0.5, 0), n, LEN(timeInput), h, IF(n < 4, LEFT(timeInput, n - 1), LEFT(timeInput, n - 3)), m, IF(n < 4, 0, MID(timeInput, n - 2, 2)), TIME(MOD(h, 12), m, 0) + pm ) )in which h=0 is used in place of 12.
Which all goes to show the value of audit and testing? Thank you.
Wow, thank you so much for doing this! I will try to reverse engineer it so I can apply the technique elsewhere.
If you require any help with the reverse engineering, just ask, and John (mathetes) or I would be able to help. I am more likely to offer outlandish ideas whereas John might actually help!
For example, one doesn't have to finish with LET. One can also conceal the gruesome details of a calculation, whilst improving its deployability, by using LAMBDA. In the attached, I have written a LAMBDA function 'ConvertToTimeλ' that converts your text time format to standard Excel times but will also accept a time parameter in a standard format. This is called by another LAMBDA function 'Durationλ' that calculates elapsed times of up to 24hrs. An optional parameter allows the user to see the start and end times for debugging or conceal them by setting an optional parameter.
Bear in mind that you do not have to try any of this, but it exists if you were ever to need it or even if you were to find it fun!
Ha!
I was actually thinking when I first saw your LET solution, PeterBartholomew1 , that one could make a neat LAMBDA out of it. So well done. I am NOT going to try to improve on anything you've done here.
