Convert time to time zone

An excel formula to convert time to time zone

Related Functions

Sponsored Link

 Convert time to time zone
=MOD(time+(hours/24),1)

To convert a time from one time zone to another, you can use a formula that converts hours entered as whole numbers to the decimal values that Excel recognizes as times.

In the example shown, the formula in F5 is:

 
=MOD(C5+(E5/24),1)

This formula returns a number that Excel recognizes as 2:00 AM.

How this formula works

Times in Excel are fractional values of the number 1. So, 12 PM is 12/24 = .5, 6:00 AM is 6/24 = .25, and so on. So, to convert a time by a given numbers, you need to divide the number of hours by 24 to get required decimal value:

 
E5/24 // convert adjustment to Excel time

We add the result to the starting time:

 
C5+(E5/24)

To make sure we have a true time value, we need to ensure that we have only a decimal value. In other words, if we add 12 hours (.5) to 6 PM (.75) we'll get 1.25, but we really only want .25.

To make sure we get just the decimal value, we use MOD with a divisor of 1, as a clever way to keep the formula simple.

MOD returns the remainder after division, so returns the decimal value in cases where the result is greater than 1 (i.e. greater than 24 hours).

Even better, if we end up with a negative fractional value, MOD returns the reciprocal. So, if we end up with -.25, MOD returns .75 (equivalent to 6 PM).

This is important, because Excel won't display negative time values.

Datetimes

If you're working with dates + times (i.e. values that include both a date and a time together), you don't want to use MOD. You can simply use:

 
=datetime+(hours/24)

This will let the date value change as needed (forwards or backwards) when time adjustments cross 12:00 AM.
 

Sponsored Link

0 votes. 0 / 5