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".
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!