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".
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.
That's really cool. I looked into Lambdas after some other comments mentioned it. My main question right now is, how does the LET function know what times to use? How do I edit the range of data it's pulling from? I'm not even sure what keywords I would use to search for that answer. My guess is that it's some kind of array or table, (features that I haven't used before)?
Both the LET function and, later, the LAMBDA functions reference the data in range Sheet1!$B$7:$C$C13 using the Defined Name 'timeInput'.
To view or edit the name, use 'Formulas / Name Manager'. The naming functionality was introduced by Microsoft in 1993 to improve the readability of spreadsheets but remains under-used. You mention the possibility of the name referring to a Table; that would indeed be an improvement, especially if the date range is to grow dynamically as further data is added.
'Normal practice in spreadsheet development is to focus upon the use of primitive highly-manual methods and, for that reason, it is unusual to find a spreadsheet that is error-free (by that I mean that about 1 in 12 spreadsheets are found to be correct when audited). Of course every spreadsheet writer is convinced that they are the exception and their spreadsheet is the one that checked out as correct!