Share via

how can I convert a date and time in GMT to Central time?

Anonymous
2012-07-21T07:11:16+00:00

Using Excel I have a column of dates that read like this:

2011-10-28 02:38:12

They are in GMT and I want to convert them to Central time.  I tried using the formula =A1-TIME(5,0,0) after splitting the dates and times in separate cells.  That seems to work for PM time but not AM time.  I'm not sure if its the way I have the GMT time cell formulated or what but when I try to apply it to times like the one above, it tells me it's something like -2:19 AM.  It changes the minutes as well as the hours so I can't figure out what it is doing there.  Any help?

Even assuming the -TIME(5,0,0) was working correctly, is there any way to attach it to the dates as well?  For instance, the above 2011-10-28 02:38:12 GMT should be October 27, 2011 9:38 PM Central.  If I have to split out the time into a different cell then the dates aren't going to match up right because I'd have a row telling me that it was 10/28 | 9:38 PM, right?

Any help greatly appreciated!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-07-21T07:37:24+00:00

    You need to make your adjustment to a value that is a combined date and time, otherwise the subtraction can result in a negative number which is not allowed.  Format your cells to just display the hours and minutes if you need to.

    If you have a true date/time of October 28, 2011 02:38:12 in A1, then

    =A1-TIME(5,0,0)

    should give you the expected result.

    Here are just 2 of many, many references on the subject:

    http://www.exceltip.com/st/Convert_Date_and_Time_from_GMT_(Greenwich_Mean_Time)_to_CST_(Central_Standard_Time)/956.html

    http://answers.microsoft.com/en-us/office/forum/office_2003-excel/how-do-i-convert-gmt-to-local-time/b0d5733d-007d-4505-992f-2742780a3ef5

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-10-23T15:24:10+00:00

    You need to make your adjustment to a value that is a combined date and time, otherwise the subtraction can result in a negative number which is not allowed.  Format your cells to just display the hours and minutes if you need to.

    If you have a true date/time of October 28, 2011 02:38:12 in A1, then

    =A1-TIME(5,0,0)

    should give you the expected result.

    Here are just 2 of many, many references on the subject:

    http://www.exceltip.com/st/Convert_Date_and_Time_from_GMT_(Greenwich_Mean_Time)_to_CST_(Central_Standard_Time)/956.html

    http://answers.microsoft.com/en-us/office/forum/office_2003-excel/how-do-i-convert-gmt-to-local-time/b0d5733d-007d-4505-992f-2742780a3ef5

    Just to help with this old thread...  If your original time is in cell A1, you can NOT use =A1-TIME(5,0,0) because you get negative times. But you CAN use =A1+TIME(19,0,0) for time ONLY. Then you never get a negative number and everything works. The cells will have to be formatted as time, not date. If the date is also in the original cell value, you can display it into a different column formatted as date. Also, if the "time" value that you want to correct is the result of a formula, or if it is actually "text" (regardless of how the cell is formatted), you will need to extract the time as a value using =VALUE(A1) or perhaps =TIMEVALUE(A1). THEN you can use the =A1+TIME(18,0,0) formula to convert UTC or GMT to your local time. Obviously the +"18" corresponds to -6 hours... and for -5 hours you would use +"19".

    -pete

    0 comments