Forum Discussion

Copper Contributor

Jan 03, 2024

Solved

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

excel

Formulas and Functions

PeterBartholomew1

Jan 03, 2024

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

Non standard time.xlsx15 KB

GeorgieAnne

Iron Contributor

Jan 04, 2024

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

ajan9626
Copper Contributor
Jan 04, 2024
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.