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)?
