Convert UTC to EST in Excel

To convert Coordinated Universal Time (UTC) to Eastern Standard Time (EST) in Excel we first must know the time difference between the two.

Universal Coordinated Time (UTC)

Coordinated Universal Time is the main time standard by which the world regulates time and clocks. It divides time into days, hours, minutes, and seconds. It is not adjusted for daylight-saving time.

Time zones in the world are expressed by either negative or positive offsets from UTC.

Eastern Standard Time (EST)

The Eastern Standard Time is five hours behind UTC during autumn and winter. During the daylight-saving time of spring and summer, the Eastern Daylight-saving Time (EDT) is four hours behind UTC.

UTC to EST/EDT Chart

The following data range shows a time conversion chart of the UTC to EST/EDT in the 24-hour format.

In this tutorial, we will explain how to convert UTC to EST/EDT in Excel by recreating the above time conversion chart.

Convert UTC to EST/EDT in Excel using formulas

We use the following steps to recreate the chart:

  1. Insert a blank row below row 2 by selecting row 3, right-clicking it, and choosing Insert from the shortcut menu.
  1. Select and delete the EDT and EST data from the dataset.
Table

Description automatically generated

Note: The UTC data includes the date though invisible. The data has been formatted to display only the time part of the day.

  1. Select cell B3 and cell C3 and type in 4 and 5 respectively. These values are the time differences between UTC and EDT and EST.
Table

Description automatically generated
  1. Select cell B4 and type in the formula:
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.
  1. Select cell C4 and type in the formula:
Table

Description automatically generated
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.

We have recreated the time conversion chart and learned how to convert UTC to EST/EDT.

Explanation of the formulas

The cell reference B3 is locked and made an absolute reference by adding the dollar sign to the column and row number. This makes it not change as the formula is copied down the column. The value in cell B3 is divided by 24 to convert it to a time value because time is a fraction of a day. The result is then subtracted from the value in cell A4 to get the EDT.

The cell reference C3 is locked and made an absolute reference by adding the dollar sign to the column and row number. This makes it not change as the formula is copied down the column. The value in cell C3 is divided by 24 to convert it to a time value because time is a fraction of a day. The result is then subtracted from the value in cell A4 to get the EST.

Conclusion

In this tutorial, we have learned how to convert Universal Coordinated Time (UTC) to Eastern Standard Time and Eastern Daylight-saving Time (EDT) in Excel using formulas.

We have created a time conversion chart that one can refer to when they want to convert UTC to EST/EDT.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.