Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Convert date time from UTC to EST

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,847
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Windows10, Excel 365 Insider
    My formulas are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    New Member
    Join Date
    Dec 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Dec 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,847
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Dec 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert date time from UTC to EST

    Thank you for putting me on the right track!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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