Break hours by minutes instead of decimals when formating a cell, but keeping the formula intact. Is that possible?

Question

I recently used this formula for time calculation within one cell and it worked great!

But I remained with a top up question: Is there a way to allow breakdown hours?

For example, using this:

9pm - 4:30pm

Instead of this:

9pm - 4.5pm

I know I could type 4.5pm, but I'm afraid people would read 4:50pm instead of 4:30pm.

Answer 1

It looks to me like those formulas don't parse times in the format 3:24pm. So no, not while keeping the formula intact.

I believe I've got the formulas working, using TIMEVALUE instead of TIME.

---

Edit:

@FlexYourData points out a bug in my original answer (at the bottom); part of this is due to my unintentional omission of the pm from the parsing.

The other part is that TIMEVALUE doesn't seem to like times without spaces.

So here's a version that doesn't care about spaces.

Cell B2:

=TIMEVALUE(LEFT(A2,FIND("-",A2)-3)&" "&MID(A2,FIND("-",A2)-2,2))

Cell C2:

=TIMEVALUE(MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)-2)&" "&RIGHT(A2,2))

Cell D2—no change to this one:

=IF(C2>B2,C2-B2,C2+1-B2)*24

---

Original answer (with minor corrections). These formulas only work with 3:24 pm and 3 pm times--note the space. I like them, though, because they're simpler. Perhaps you could require times to be entered in this format.

Cell B2:

=TIMEVALUE(LEFT(A2,FIND("-",A2)-1))

Cell C2:

=TIMEVALUE(MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)))

Cell D2—no change to this one:

=IF(C2>B2,C2-B2,C2+1-B2)*24

Answer 2

Doing this with formulas is somewhat painful, so I've used LET to hopefully make it easier to read. It would be much easier to use PowerQuery, but I think this will work. If you have access to LAMBDA, which unfortunately I don't, I recommend using that.

First of all, I've defined the start/end separator, i.e. " - ", as a Named Cell called "sep" in cell B2:

I've done that so if you want to change it to "-" or something else in the future, you won't have to change it in several places in formulas, but rather just in that one cell.

With Helper Columns for Start and End

The approach here is broadly:

1. determine the position of the separator in the string and call that "pos"
2. return the time as text and call it "str"
3. determine if the time as text contains minutes (indicated by presence of a colon) and call that "hasmin"
4. calculate the time representation of "str", which is modified from the original answer you used, but taking different action depending on whether "hasmin" is TRUE or not

Start time formula in B3:

=LET(pos,FIND(sep,$A3),
str,LEFT($A3,pos1),
hasmin,NOT(ISERROR(FIND(":",str))),
TIME(LEFT(str,IF(hasmin,FIND(":",str)-1,LEN(str)-2))+IF(RIGHT(str,2)="pm",12,0),IF(hasmin,MID(str,FIND(":",str)+1,2),0),0))

End time formula in C3:

=LET(pos,FIND(sep,A3),
str,MID(A3,pos+LEN(sep),LEN(A3)),
hasmin,NOT(ISERROR(FIND(":",str))),
TIME(LEFT(str,IF(hasmin,FIND(":",str)-1,LEN(str)-2))+IF(RIGHT(str,2)="pm",12,0),IF(hasmin,MID(str,FIND(":",str)+1,2),0),0))

Hours elapsed formula in D3:

=IF(C3>B3,C3-B3,C3+1-B3)*24

As a single formula

Here I have just combined the two "Start Time" and "End Time" columns into the same LET, calling them "from_time" and "to_time". The last expression, i.e. the result, is then just the formula in the "Hours elapsed" column, but referring to the names in the LET function instead of the columns.

In E3:

=LET(break_pos,FIND(sep,$A3),

from_text,LEFT($A3,break_pos-1),
from_has_mins,NOT(ISERROR(FIND(":",from_text))),
from_time,TIME(LEFT(from_text,IF(from_has_mins,FIND(":",from_text)-1,LEN(from_text)-2))+IF(RIGHT(from_text,2)="pm",12,0),IF(from_has_mins,MID(from_text,FIND(":",from_text)+1,2),0),0),

to_text,MID($A3,break_pos+LEN(sep),LEN($A3)),
to_has_mins,NOT(ISERROR(FIND(":",to_text))),
to_time,TIME(LEFT(to_text,IF(to_has_mins,FIND(":",to_text)-1,LEN(to_text)-2))+IF(RIGHT(to_text,2)="pm",12,0),IF(to_has_mins,MID(to_text,FIND(":",to_text)+1,2),0),0),

IF(to_time>from_time,to_time-from_time,to_time+1-from_time)*24
)