Convert an offset clock time to UTC

Turn the text 14:30:15+05:30 into the UTC clock time 09:00:15.

What you'll make

In Excel: 14:30:15+05:30 becomes 09:00:15 UTC clock.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TIMEVALUE( … )Reads the first 8 characters as a clock time.14:30:15
2IF( … )Gives 1 for a plus sign, -1 for minus.1
3( … )/1440Turns the offset into part of a day, then takes it away.09:00:15
4MOD( … ,1)Wraps round midnight so the time stays within one day.09:00:15

A day has 1,440 minutes. A plus offset is ahead of UTC, so it comes off; a minus offset is added.

=MOD(TIMEVALUE(LEFT(A8,8))-IF(MID(A8,9,1)="+",1,-1)*(MID(A8,10,2)*60+RIGHT(A8,2))/1440,1)

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 clock times like 14:30:15+05:30 into the green column of the Template tab. The UTC times fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

There is no date, so a time that crosses midnight doesn't say which day. If the day matters, use the full timestamp instead.

Microsoft's help: MOD function