# Thread: Convert date time from UTC to EST Thanks: 0 Likes: 0

1. ## Convert date time from UTC to EST

Hello,

My first post! I am trying to convert spreadsheet from UTC time and date to EST. My formulas for Column C is =MOD(B13-(5/24),1). But for the first 4 rows the time does not convert to 11/11/2016 as it should be. Another way I tried was =B13 -"05:00:00" which also converts the time but not the date. Can anyone help?

 Date Time EST 11/12/2016 00:53:37 19:53:37 11/12/2016 00:54:09 19:54:09 11/12/2016 00:54:45 19:54:45 11/12/2016 00:57:01 19:57:01 11/12/2016 01:56:27 20:56:27 11/12/2016 02:20:43 21:20:43 11/12/2016 02:51:21 21:51:21 11/12/2016 09:10:54 04:10:54

2. ## Re: Convert date time from UTC to EST

Welcome to the forum.

I think this is what you're after. I have formatted columns C and D as a Custom format (m/d/yyyy h:mm:ss).

A B C D
1 UTC Date UTC Time UTC D&T EST D&T
2 11/12/2016 0:53:37 11/12/2016 0:53:37 11/11/2016 19:53:37
3 11/12/2016 0:54:09 11/12/2016 0:54:09 11/11/2016 19:54:09
4 11/12/2016 0:54:45 11/12/2016 0:54:45 11/11/2016 19:54:45
5 11/12/2016 0:57:01 11/12/2016 0:57:01 11/11/2016 19:57:01
6 11/12/2016 1:56:27 11/12/2016 1:56:27 11/11/2016 20:56:27
7 11/12/2016 2:20:43 11/12/2016 2:20:43 11/11/2016 21:20:43
8 11/12/2016 2:51:21 11/12/2016 2:51:21 11/11/2016 21:51:21
9 11/12/2016 9:10:54 11/12/2016 9:10:54 11/12/2016 4:10:54
Sheet61

Worksheet Formulas
Cell Formula
C2 =A2+B2
D2 =C2-5/24

3. ## Re: Convert date time from UTC to EST

I custom formatted by C column with mm/dd/yyyy hh:mm:ss and my results are: 09/26/2133 01:47:14, I am doing something wrong and then C is formatted =A2+B2. I've got a couple of Mr. Excel books that I'm checking but still don't see what I am doing wrong.

4. ## Re: Convert date time from UTC to EST

I think I have it figured out- Both Column A & B show Date and Time so that portion is already calculated - so I just needed the second formula and it is now correct! thanks again for your assistance.

5. ## Re: Convert date time from UTC to EST

Dates and times in Excel present challenges. It's hard to know sometimes just what is in the cell. I'm glad you worked it out.

6. ## Re: Convert date time from UTC to EST

Thank you for putting me on the right track!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•