Build a signed duration from its parts

Turn a minus sign with 49 hours, 30 minutes and 15 seconds into -2.062674 days.

What you'll make

In Excel: -, 49, 30 and 15 becomes -2.062674 days.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="+",A8="-")Checks the sign is a plus or a minus.TRUE
2IF(A8="-",-1,1)Gives -1 for a minus, 1 for a plus.-1
3(B8/24+C8/1440+D8/86400)Turns the hours, minutes and seconds into days.2.062674
4"Review duration sign"Shown when the sign is anything else.Not used for row 8
5IF( … )Puts the sign on the days, or flags the row.-2.062674

Excel counts time in days: a day has 24 hours, 1440 minutes and 86400 seconds. The sign covers the whole time, so - with 0 hours and 30 minutes stays negative.

=IF(OR(A8="+",A8="-"),IF(A8="-",-1,1)*(B8/24+C8/1440+D8/86400),"Review duration sign")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own durations. Paste the sign (+ or -), hours, minutes and seconds into the green columns of the Template tab. The days fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Keep these results as plain numbers. Excel shows #### for a negative time in a time format.

Microsoft's help: IF function