Forum Discussion

ajan9626's avatar
ajan9626
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's avatar
    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".

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
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".

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 05, 2024
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.

Resources