Convert Time Zone with Daylight Savings in Excel (2 Cases)

If you are searching for a solution or some special tricks to convert time zone to daylight savings in Excel then you have landed in the right place. There are some quick methods to convert time zone to daylight savings in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.

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

In this section, I will show you 4 quick and easy methods to convert time zone to daylight savings in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

✅ When Date and Time Are in One Cell

Often, you have time and date in one cell and often in different cells. I will show you the solution for both cases. Now, I will show you the method to convert the time zone to DLS time when the cell contains both time and date together.

Method 1: Using Formula

In the first method, I will show you the method using a generic formula. For this follow the steps below:

📌 Steps:

First, insert the DLS hour in cell F5.

Then, paste the formula given below in cell D5.

=C5-$F$5/24

🔎 Formula Explanation

Here, cell C5 is the cell that contains the time and date value in the normal time zone.
Cell F5 contains the DLS hours applied for the country.
$F$5/24 converts the hour to day value and you have to make the cell reference of F5 cell Aboslute so it will befilled for all cells applied.

Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

As a result, you will see that, the “Daylight Saving” column is filled with the output of the formula applied. Thus you have got the DLS time of input values.

Method 2: Using TIME Function

Alternatively, you can use the TIME function to do the same task when the date and time are in the same cell together. For this follow the steps below:

📌 Steps:

For this case, paste the following formula into cell D5.

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

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

So, by this formula, you subtracted the time in cell C5 by 2 hours which is the DLS value inserted in cell F5.

Now, drag the fill handle icon to paste the similar formula to the other cells of the column.

✅ When Date and Time Are in Different Cells

Now, here I will show the methods to convert the time zone to the Daylight savings system when you have date and time values in different columns.

Method 1: Using Formula to Get DLS Time and Date in Same Cell

In this method, I will use a simple formula without any function to get DLS time and date in the same cell. For this-

📌 Steps:

Insert the following formula in the cell E5:

=C5+D5- $G$5/24

With this formula, first, you are combining the date and time value and then subtracting the DLS hour from it. Thus, you will get the DLS time as the output.

Drag the Fill Handle icon till the last cell of the column and you will get the column filled with DLS time for the respective date and time.

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

Now, if you want to get the DLS date and time value in different cells then, you have to use the INT and the MOD functions. For this, follow the steps below-

📌 Steps:

First, create two columns to get DLS date and time outputs.

Insert the following formula into cell E5 to get the DLS Date value of the input data.

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

Through this formula. First, you combine the date and time value then, subtract the DLS hour from it. Then, you have to use the INT function to round them and you will get the date value of DLS time zone.

Then, you have to find the time value of the DLS time zone. For this, insert the following formula into cell F5.

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

Through this formula, similarly, you have combined the input date and time value and then subtracted the DLS hour. After that, you have used the MOD function to get the Time value which is the remainder of the previous output.

Now, select the two cells E5 and F5 then drag the Fill Handle icon to the last cell of the table.

As a result, you will get the columns filled with the DLS date and time.

Things to Remember

You have to divide the DLS hour by 24 to convert it into numerical time format.
If you face the format issue then select the cells and change the formats to “Date” or “Time” as per the column value.

Download Practice Workbook

You can download the practice workbook from here:

Convert Time Zone Daylight Savings.xlsx

Conclusion

In this article, you have found how to convert time zone to daylight savings in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Convert Time Zone with Daylight Savings in Excel (2 Cases)

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

✅ When Date and Time Are in One Cell

Method 1: Using Formula

Method 2: Using TIME Function

✅ When Date and Time Are in Different Cells

Method 1: Using Formula to Get DLS Time and Date in Same Cell

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

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply