How to format time duration in a mixed string/number cell?

Question

I have a cell that I would like to format as follows (and with the desired values):

3:15 / 48:00 (-44:45)

Here is what I have tried (attempting to the use the TEXT function):

=text(sum(B4:4)*24,"hh:mm")&" / "&text(sum(B1:1),"hh:mm")&" ("&text( (sum(B4:4)*24)-sum(B1:1),"hh:mm" )&")"

However that gives an output of:

06:00 / 00:00 (06:00)—the numbers are quizically wrong!

doubleunary · Accepted Answer · 2023-02-23 10:19:48Z

1

You need to format row 1 as Format > Number > Duration.

To format an elapsed time as duration properly, use the [h] pattern in text(), like this:

=text(sum(B4:4), "[h]:mm") & " / "& text(sum(B1:1), "[h]:mm") & " (" & text(sum(B4:4) - sum(B1:1), "[h]:mm") & ")"

answered Feb 23, 2023 at 10:19

doubleunary

14.5k5 gold badges20 silver badges52 bronze badges

Yes I also learned that I could get it to work if my source cells were Duration formatted! Do you know if there is a way to do it starting with regular numbers instead?
– Drewdavid
Feb 23, 2023 at 23:09
You can convert an integer into a duration of that many hours with value(int(H2) & ":00"). Please ask only one question per post. Post a new question instead.
– doubleunary
Feb 24, 2023 at 6:56
See What should I do when someone answers my question?
– doubleunary
Feb 24, 2023 at 6:56

Add a comment |

1 Answer 1