Read a signed hh:mm offset as minutes

Turn the offset text -03:30 into a number of minutes: -210.

What you'll make

In Excel: -03:30 becomes -210 minutes.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1IF( … )Gives -1 for a minus sign, 1 for plus.-1
2MID( … )*60Turns the two hour digits into minutes.180
3RIGHT( … )Adds the last two digits, the minutes.-210

Parts 2 and 3 make 210 minutes. The * multiplies that by part 1, so -03:30 gives -210.

=IF(LEFT(A8,1)="-",-1,1)*(MID(A8,2,2)*60+RIGHT(A8,2))

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 offsets. Paste offsets like -03:30 into the green column of the Template tab. The minutes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

It doesn't check for real time zones, so +25:00 still gives 1500. Look over odd offsets.

Microsoft's help: LEFT function