Forum Discussion
How to convert my unique time format to excel time format
Hey party people,
I have a particular way that I record time in a text editor throughout the day. I would rather be putting these times into excel for further calculations. I use a style of time that is very easy for me to record (one-handedly), aka I want to avoid typing colons and caps letters.
In this picture, line 3 represents how I like to write it while line 2 shows the format I would like it to be converted to.
I use columns C (excel format: HH:MM AM/PM) to convert A (excel format: Number, with "=A2") to an excel time value before calculating duration in E ( using "=(C1-D1)*24" ).
TLDR: How can I type "845a" and "130p" and get the value "4.25"?
I thought I was an intermediate level user but now I'm doubting that, haha.
Thanks in advance!!
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".
12 Replies
Hello ajan9626
You say "I use a style of time that is very easy for me to record (one-handedly), aka I want to avoid typing colons and caps letters."
which is understandable, I mean the programmers of the telephone systems back in the day chose 9-1-1 as the emergency response number in the United States for ease of dialing (on a rotary phone) I did say back in the day...
So, following the same concept why not use
CTRL+SHIFT+;
Try it! It will give you the time in the Excel-flavored format, easy to type with one hand (using the Right-side keys), and accurate if you want to go to the seconds granularity (using the Excel Time Formats)
Oh CTRL+; will also give you the date! Imagine that...
Georgie Anne
Dang, those are some useful hotkeys. Thanks for sharing! Unfortunately, it's not always real-time when I enter the time intervals- if it was, then this would be perfect.
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!
Before attempting to answer (a semi-advanced intermediate user here), let me ask how granular you need to be. It would not be all that difficult to construct a table that could convert "845a" and "900a" and so on, 15 minute intervals. There are, after all, only four per hour. But if you wanted precision at the level of every 10 minutes, every five, or--perish the thought--every minute, then you'd be talking of a more monumental effort.
There may be simple ways, but I thought it worthwhile to ask, since you're basing this whole request on ease of use, how much of a dog this tail is going to have to wag.
Hi, thanks for the response. Yes, 15 min intervals are all I need. That's a nice solution- one that I should have thought of! If you're curious, another user, (@peter Bartholomew), shared a solution using (what I consider to be) advanced functions and formulas. It's pretty cool, you should check it out. (scroll down)
FYI, all of the responses are visible to each person who's replied on any given thread. And my friend PeterBartholomew1 can always be relied on for a creative use of LET, which is truly a wonderful addition to the Excel toolbox.
