How to Convert Time Zone with Daylight Savings in Excel

Case 1 – Date and Time Are in One Cell

Convert the time zone to DLS time when the cell contains both time and date.

Excel Convert Time Zone Daylight Savings

Method 1 – Using a Formula

Steps:

Enter the DLS hour in F5.

Convert Time Zone When Date and Time Are in One Cell

Use the formula below in D5.

=C5-$F$5/24

Formula Breakdown

C5 contains the time and date value in the normal time zone.
F5 contains the DLS hours applied to the country.
$F$5/24 converts the hours to day value. Set the cell reference of F5 Absolute.

Inserting Formula When Date and Time Are in One Cell

Drag down the Fill Handle to see the result in the rest of the cells.

Drag Fill Handle

The “Daylight Saving” is displayed.

Output When Date and Time Are in One Cell

Method 2 – Using the TIME Function

Steps:

Enter the following formula in D5.

=C5-TIME($F$5,0,0)

Syntax of the TIME function: =TIME(hour, minute, second)

The formula subtracts 2 hours from the time in C5 (the DLS value in F5).

Using Time Function

Drag down the Fill Handle to see the result in the rest of the cells.

Output Using Time Function

Case 2 -Date and Time Are in Different Cells

Convert the time zone to the Daylight savings system:

When Date and Time Are in Different Cells

Method 1 – Using a Formula to Get DLS Time and Date in the Same Cell

Steps:

Enter the following formula in E5:

=C5+D5- $G$5/24

The formula combines the date and time values and subtracts the DLS hours from it.

Using Formula to Get DLS Time and Date in Same Cell

Drag down the Fill Handle to see the result in the rest of the cells.

Using Formula to Get DLS Time and Date in Same Cell

Method 2 – Using the INT and MOD Functions to Get the DLS Time and Date in Different Cells

Steps:

Create two columns to get the DLS date and time outputs.

Dataset to Convert Time Zone Daylight Savings

Enter the following formula in E5 to get the DLS Date.

=INT(C5+D5-$H$5/24)

The formula combines the date and time value and subtracts the DLS hours from it; the INT function rounds the values and returns the date value of the DLS time zone.

Using INT Function to Get DLS Date

To find the time value of the DLS time zone, use the following formula in F5.

=MOD(C5+D5-$H$5/24,10)

The formula combines the input date and time value and subtracts the DLS hours; the MOD function gets the Time value: the remainder of the previous output.

Using MOD Functions to Get DLS Time

Select E5 and F5 and drag the Fill Handle icon to the last cell of the table.

Drag Fill Handle

You will see the columns filled with the DLS date and time.

Excel Convert Time Zone Daylight Savings

Things to Remember

You have to divide the DLS hour by 24 to convert it into numerical time format.
If you face format issues, select the cells and change the format to “Date” or “Time”.

How to Convert Time Zone with Daylight Savings in Excel – 2 Cases

Case 1 – Date and Time Are in One Cell

Method 1 – Using a Formula

Method 2 – Using the TIME Function

Case 2 -Date and Time Are in Different Cells

Method 1 – Using a Formula to Get DLS Time and Date in the Same Cell

Method 2 – Using the INT and MOD Functions to Get the DLS Time and Date in Different Cells

Things to Remember

Related Articles

Leave a reply Cancel reply