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.


Download Practice Workbook

You can download the practice workbook from here:


How to Convert Time Zone with Daylight Savings in Excel

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.

Excel Convert Time Zone Daylight Savings


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.

Convert Time Zone When Date and Time Are in One Cell

  • 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.

Inserting Formula When Date and Time Are in One Cell

Drag Fill Handle

  • 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.

Output When Date and Time Are in One Cell


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.

Using Time Function

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

Output Using Time Function


✅ 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.

When Date and Time Are in Different Cells


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.

Using Formula to Get DLS Time and Date in Same Cell

  • 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

Using Formula to Get DLS Time and Date in Same Cell

Read More: Making a List of Countries by Time Zone in Excel (with Easy Steps)


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.

Dataset to Convert Time Zone Daylight Savings

  • 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.

Using INT Function to Get DLS Date

  • 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.

Using MOD Functions to Get DLS Time

  • Now, select the two cells E5 and F5 then drag the fill handle icon to the last cell of the table.

Drag Fill Handle

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

Excel Convert Time Zone Daylight Savings

Read More: How to Create World Time Zone Clock in Excel (2 Easy Methods)


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.

Conclusion

In this article, you have found how to convert time zone to daylight savings in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo