Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Converting DateTimeUTC to CST

0

I am trying to convert a DateTimeUTC value to CST.  Here is an example of the DateTimeUTC Value that I am trying to convert:

DateTimeUTC
1258119969

I have had some success with this formula - =(((D:D/60)/60)/24)+DATE(1970,1,1) (where D equals the cell with the UTC time in it) but it doesn't get me to CST - I can't figure out how to subtract the six hours needed.

DateTimeUTC   Converted Time
1258119980      11/13/09 1:46 PM
1258120491      11/13/09 1:54 PM

Any thoughts?

Answer
Discuss

Answers

0

Once you get the date/time value, just use the TIME function to subtract the hours.

=B1-TIME(6,0,0)

This assumes that cell B1 contains the date time that you got from the formula you included.

The 6 within the TIME function represents the hours that you want to subtract from the time.

Discuss

Discussion

So this becomes my new formula - =(((D:D/60)/60)/24)+DATE(1970,1,1)-TIME(6,0,0) (where D equals the cell with the UTC time in it) and it works great. 

I have two more questions.  There are some cells under the DateTimeUTC that are blank.  Can an "if" statement be added to the formula telling it to skip blank cells?  And is there a way to adjust the formula to allow for daylight savings time.  So if I had a years worth of line items, it would adjust the time accordingly.  Thanks again for helping me finally get my formula right!
skbryan Jun 7, '17 at 9:26 am
Yea, just do a simple =IF(isblank(A1),TRUE,FALSE) check but replace TRUE with what you want to happen if the cell is blank and FALSE with the formula you are using.

For the daylight savings time, you will have to use a formula to adjust for that; that specific issue is better suited to a separate question in the forum.
don (rep: 1989) Jun 8, '17 at 10:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login