How to Convert UTC to EST in Excel: 7 Easy Formulas
Prologue
In this article, you’ll learn about UTC to EST conversion in Excel. This covers 7 formulas to do so. You will also learn how to convert UTC to EDT, deal with daylight saving time, convert UTC to PST, convert UTC to local time, and convert EST to UTC.
The world has several time zones. Learning how to convert one time zone to another is essential if you deal with international clients.
If you plan to visit a foreign country, then you must know their time. Now, you can just search for the time for that period, but using Excel will give you customization and flexibility.
You can also enhance your Excel knowledge by solving real world problems.
What Do UTC and EST Mean?
Before starting all the conversions from UTC to EST, let’s discuss UTC and EST in brief.
UTC
UTC means Coordinated Universal Time. This is a time standard to maintain accuracy and consistency across the world’s time zones. The International Bureau of Weights and Measures (BIPM), which employs atomic clocks, maintains this. This time is not affected by daylight saving time. Here are a few sample formats that represent UTC:
UTC Time Format | Meaning |
Friday, 28-Jul-2023 05:46:35 UTC | 5:46:35 AM on July 28, 2023 |
2023-28-07 05:46:35 AM | 5:46:35 AM on July 28, 2023 |
17:30:45 UTC | 17 hours, 30 minutes and 45 seconds |
2023-07-28T14:30:00 | The time is 2:30 PM on July 28, 2023. Here, T denotes the time separator with the date |
2023-07-28T05:46:35Z | This means July 28, 2023, at 5:46:35 AM. The “Z” at the end indicates that it is in UTC and does not have a time zone offset. |
2023-07-28T05:46:35+02:00 | This indicates July 28, 2023, at 5:46:35 AM in a time zone that is 2 hours ahead of UTC. |
1690523195 | Number of seconds elapsed after January 1, 1970. This is known as UNIX Epoch Time. |
EST
EST means Eastern Standard Time. This time zone is 5 hours behind UTC (UTC-5). The eastern part of North America uses this time zone. For example, 12:30 PM in UTC will be 7:30 AM in EST. The following table shows the states that use this time zone:
Zone | State/Territory |
Canada | Ontario, Quebec, and Nunavut |
Caribbean Islands | Bahamas, Cayman Islands, Cuba, Jamaica, Turks and Caicos Islands |
Mexico | Quintana Roo |
South America | Brazil (Acres, Amazonas), Colombia, Ecuador (except Galápagos Islands), Panama, Peru |
USA | Connecticut, Delaware, Florida*, Georgia, Indiana*, Kentucky*, Maine Maryland, Massachusetts, Michigan*, New, Hampshire, New Jersey, New York, North Carolina, Ohio, Pennsylvania, Rhode Island, South Carolina, Tennessee*, Vermont, Virginia |
*means mixed time zone.
Convert UTC to EST in Excel
You’ll find 7 formulas to convert UTC to Eastern Standard Time in Excel.
Use Subtraction to Convert UTC to EST
You’ll learn how to use a generic subtraction formula to convert UTC to Eastern Standard Time in Excel.
- Apply the following formula to cell C3.
=B3-5/24
Apply TIME Function to Change UTC to EST
In this section, we’ll show you how to use the TIME function to convert UTC to EST.
- Insert the following formula in cell C3 and press Enter.
=B3-TIME(5,0,0)
Convert ISO-8601 UTC to EST
In this section, UTC is given in ISO-8601 time format. We’ll use the SUBSTITUTE function along with the LEFT, MID, and RIGHT functions to convert UTC to EST.
- Firstly, type this formula in cell C3 for positive offset.
=SUBSTITUTE(LEFT(B3,19),”T”,” “)+MID(B3,21,2)/24+RIGHT(B3,2)/1440
- Secondly, use this formula in cell C6 for negative offset.
=SUBSTITUTE(LEFT(B6,19),”T”,” “)-MID(B6,21,2)/24-RIGHT(B6,2)/1440
Decoding Formula
- LEFT(B3,19) ↔ “2023-07-12T17:46:35”.
- This returns the first 19 strings from the left side.
- SUBSTITUTE(“2023-07-12T17:46:35″,”T”,” “) ↔ “2023-07-12 17:46:35”.
- Replaces T with a white space.
- MID(B7,21,2)/24 ↔ 0.125
- Returns 2 strings after the 21st position.
- Then, it divides the result by 24 to convert it to hour.
- RIGHT(B7,2)/1440 ↔ 0.0083.
- Returns the 2 strings from the right side.
- After that, it divides it by 1440 to convert it into minutes.
Now, you’ll see how to utilize the Format Cells feature to change the date formatting in Excel.
- Firstly, select the cell range C3:C7.
- Secondly, select Cells from the Format tab.
- So, the Format Cells window will pop up.
- Then, select Custom from the Category sidebar.
- Afterward, type the following value in the Type box: m/d/yy h:mm AM/PM;@.
- This custom format represents month/day/year hour:minute AM/PM. Instead of “;@” you can type any string inside the double quotation to show it in the date and time formatting.
- Lastly, hit OK.
- This will change the date format in the output cells.
Combine DATEVALUE and TIMEVALUE Functions
We’ll combine the DATEVALUE, TIMEVALUE, LEFT, and MID functions to create a formula to transform UTC to EST.
- Type this formula in cell C3.
=DATEVALUE(LEFT(B3,10))+TIMEVALUE(MID(B3,12,8))-5/24
Decoding Formula
- DATEVALUE(LEFT(B3,10))+TIMEVALUE(MID(B3,12,8))-5/24
- LEFT(B3,10) ↔ “2023-07-28”.
- This returns the first 10 strings from the left side of the UTC in cell B3.
- DATEVALUE(“2023-07-28”) ↔ 45135.
- The serial number for the date July 28, 2023.
- MID(B3,12,8) ↔ “16:16:23”.
- The 8 strings after the 12th position.
- TIMEVALUE(“16:16:23”) ↔ 0.678.
- Returns the time value for 4:16:23 PM.
- Then, the formula returns the date in EST.
Convert Epoch UTC to EST in Excel
This time, the UTC is given in Epoch format. The term, also referred to as UNIX time, is used to express time as the number of seconds since a particular time point, which is known as an epoch. The standard setting for the epoch is January 1, 1970, 00:00:00 UTC.
- Insert this formula in cell C3 and press Enter.
=B3/86400+DATE(1970,1,1)-(5/24)
Utilize MOD Function to Convert UTC to EST
In this method, we’ll use the MOD function to convert UTC to EST. The MOD function returns the remainder after a number is divided.
- Type this formula in cell C3.
=MOD(B3-5/24,1)
Convert Military UTC to EST in Excel
We’ll use the combination of the TEXT and MOD functions to convert UTC to EST. The UTC is provided in military time.
- Use this formula in cell C3.
=TEXT(MOD(TEXT(B3,”00\:00″)-5/24,1),”hhmm”)
Decoding Formula
- TEXT(B3,”00\:00″) ↔ “12:00”.
- This converts the military time to standard time.
- MOD(“12:00”-5/24,1) ↔ 0.29167
- TEXT(0.29167,”hhmm”) ↔ 0700
- This second TEXT function again converts the time in military time format.
How to Change UTC to EDT in Excel
When you don’t consider daylight savings, the difference between UTC and EDT (Eastern Daylight Time) is four hours. Therefore, you’ll need to adjust it using formulas.
- The first algebraic formula deducts 4 hours from UTC. This formula is inserted in cell C3.
=B3-4/24
- The second formula utilizes the TIME function to subtract four hours from UTC to convert to EDT. This formula is used in cell C6.
=B6-TIME(4,0,0)
Convert UTC to EDT with Daylight Savings
We adjust our clocks twice a year for daylight saving time, which allows us to enjoy longer evening daylight in some months. In the summer, we turn the clocks ahead, and in the winter, we turn them back. It allows us to make better use of natural light and save energy.
There is daylight saving in the EST time zone. On the second Sunday of March at 2:00 a.m. the clock is advanced by one hour, and it is reset on the first Sunday of November at 2:00 a.m. The table below summarizes this.
Actual Time | Adjusted Time |
Second Sunday of March, 2:00 AM EST | 3:00 AM EDT |
First Sunday of November, 2:00 AM EDT | 1:00 AM EST |
Now, let’s see how to use a formula to implement this in Excel.
- Firstly, you’ll need to find the start and end dates of daylight saving time.
- This formula below finds the starting day in cell C11.
=DATE(C10,3,1+MOD(15-WEEKDAY(DATE(C10,3,1)),7)+7)
- Again, this formula returns the ending day in cell C12.
=DATE(C10,11,1+MOD(15-WEEKDAY(DATE(C10,11,1)),7)+7)
- Then, type the time in cell C13.
- After that, type this formula in cell C3 to convert UTC to EDT with daylight saving time.
=IF(AND(B3>=DATE(YEAR($C$11),MONTH($C$11),DAY($C$11))+TIME(HOUR($C$13),0,0), B3<=DATE(YEAR($C$12),MONTH($C$12),DAY($C$12))+TIME(HOUR($C$13),0,0)),B3-3/24,B3-4/24)
- You may feel the formula is huge, but don’t worry; we’ll break it down for you.
- You can select years from 2019 to 2027 in cell C10. Moreover, you can change this list in the Data Validation feature.
- Finally, the following image shows that as the year changes, so do the daylight start and end dates for that year.
Decoding Formula
There are two distinct formulas to decode. The formula is the same for finding the daylight starting and ending dates. So, you’ll see the explanation of only the daylight starting day.
Decoding Formula for Daylight Starts/Ends
- The original formula is DATE(C10,3,1+MOD(15-WEEKDAY(DATE(C10,3,1)),7)+7)
- DATE(C10,3,1) ↔ 44986.
- This returns the serial number for the first day of March 2023. Here, as the month is March, we entered 3.
- WEEKDAY(46447) ↔ 4.
- This returns the weekday of the serial number.
- MOD(15-4,7) ↔ 4.
- When 11 is divided by 7, 4 is the remainder. The MOD function returns the remainder. So, it will show 4.
- Now, the formula is easier to read.
- DATE(C10,3,1+4+7) ↔ 44997.
- This is the serial number for the second Sunday of March 2023.
- The formula works similarly for finding the second Sunday of November for that year.
Decoding Formula for Daylight Saving Time
- The formula determines whether the date in cell B3 is within the daylight period. If it is, it will subtract 3 hours; otherwise, it will subtract 4 hours.
- There are two parts to this formula that are inside the AND function.
- B3>=DATE(YEAR($C$11),MONTH($C$11),DAY($C$11))+TIME(HOUR($C$13),0,0) checks if the date is more or equal to the daylight starting date.
- DATE(YEAR($C$11),MONTH($C$11),DAY($C$11)) ↔ 44997.
- This returns the serial number for the daylight starting date.
- TIME(HOUR($C$13),0,0) ↔ 0.083 or 2/24.
- It returns the hour for daylight saving time. We added this to change the time only at this hour rather than at the start of this day.
- Now, this part repeats for daylight end time DATE(YEAR($C$12),MONTH($C$12),DAY($C$12))+TIME(HOUR($C$13),0,0) ↔ 45242.083.
- Therefore, the AND function becomes small.
- AND(B3>=44997+0.083, B3<=45242.083) ↔ AND(True,False) ↔ False.
- Both of the arguments need to be true in order to return true in the AND function. So, for one false, it returned False.
- Finally, the formula reduces to IF(AND(TRUE,FALSE),B3-3/24,B3-4/24)
- Thus, it will subtract 4 hours from UTC to change it to EDT.
- Thus, this formula works.
How to Convert UTC to PST in Excel
The difference between UTC and Pacific Standard Time (PST) is 8 hours. So, you’ll need to subtract 8 hours from the UTC to convert it. Again, two formulas can be used to do this.
- Firstly, type the following formula in cell C3.
=B3-8/24
- Secondly, you can use the TIME function to do so. Use this formula in cell C6.
=B6-TIME(8,0,0)
How to Change UTC to Local Time
You need to know your local time and its difference from UTC in order to change UTC to your local time. There will be two formulas to do so.
Use VLOOKUP Formula
For the first formula, there is a list containing seven different time zones that shows the UTC time difference from the local time. You can select any location from the list in cell C9. According to the selection, the value of cell C2 will also change.
- Now, there is a VLOOKUP formula to input the time gap and return the local time.
- Use this formula in cell C3 to convert the time.
=B3+VLOOKUP($C$9,$B$11:$C$17,2,0)/24
If you’re interested in practicing the VLOOKUP function, you can check out: Excel VLOOKUP Exercises PDF with Answers
Apply Generic Formula
There are so many different time zones in the world. Listing them will be a tough task. So, in this section, you’ll learn how to use a generic formula to convert UTC to local time.
- Firstly, input the time gap from UTC to your local time in cell C9.
- Lastly, type the following formula in cell C3.
=B3-$C$9/24
How to Convert EST to UTC in Excel
Now, you’ll see how to get the reverse result of this article- convert EST to UTC in Excel with two formulas.
- Firstly, type this formula in cell C3 and hit Enter.
=B3+5/24
- It will return the UTC for that value.
- Secondly, use another formula in cell C6 to convert that to UTC.
=B6+TIME(5,0,0)
Download Section
Download the Excel file for this article from here.
Answers to Common Questions
What is the difference between UTC and GMT?
UTC is often confused with GMT. However, UTC is a time standard, and GMT is a time zone for eastern European countries. UTC is more accurate than GMT.
What is military time, and how do I convert UTC to military time?
The 24-hour time format is known as military time. To avoid confusion, this time is used. It doesn’t require you to declare AM or PM with the time. Midnight 00:00 is represented by 24:00 in military time. 11:17 PM in military time is 23:17. You can use the TEXT function or the custom number formatting feature to convert UTC to military time.
How do I convert UTC to IST in Excel?
Suppose you have your UTC in cell B3, and the output location will be cell B4. Then, you can use the following formula in cell B4 to convert UTC to Indian Standard Time.
=B3+Time(5,30,0)
Epilogue
You’ve seen seven formulas to convert UTC to EST in Excel. If you’ve got anything to share, please feel free to comment below. You can also follow Excel Flash on Twitter and YouTube.