Read negative elapsed time like -0:30

Turn the text -0:30 into minus 30 minutes, keeping the sign even with zero hours.

What you'll make

In Excel: -0:30 becomes a negative 30-minute numeric duration.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1IF(LEFT(A8,1)="-",-1,1)Gives -1 when the text starts with a minus.-1
2ABS(TEXTBEFORE(A8,":"))Takes the hours before the colon, without the minus.49
3/24Divides the hours by 24 to count them in days.2.0417 (49:00)
4TEXTAFTER(A8,":")/1440Takes the minutes and adds them, counted in days.2.0625 (49:30)

Excel counts time in days. The * puts the sign on the whole duration, so -49:30 gives -2.0625 and -0:30 stays negative.

=IF(LEFT(A8,1)="-",-1,1)*(ABS(TEXTBEFORE(A8,":"))/24+TEXTAFTER(A8,":")/1440)

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 times. Paste elapsed times like -0:30 or 49:30 into the green column of the Template tab. The durations 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: TEXTBEFORE function