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

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

``````9pm - 4.5pm
``````

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

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
``````
• +1 for use of `TIMEVALUE`. The formulas may need some modification since TIMEVALUE requires the time_text parameter to be a text-formatted time and returns a #VALUE! error if a single integer is passed to it, as in the case of the "9" from "9pm" Jan 11, 2022 at 14:16
• @FlexYourData: Rats, you're right. I think I've fixed it. Jan 13, 2022 at 4:50

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

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
)
``````
• Interesting! Fyi, the LET function may not be available to everyone yet.
– MiG
Jan 11, 2022 at 17:10