We help IT Professionals succeed at work.

EXCEL 2007:  Computing UTC Time to My Time Zone

itsmevic
itsmevic asked
on
Hello Experts,

     I have a spreadsheet that has a column that contains the UTC time stamp stamp of when each of my users have logged into the network (Column B).  I'd like to convert this to show Central Standard Time in column C.  For example, here's what I would want it to do below:

      Column B is the UTC time, and Column C represents the converted UTC time to CST time.  Is this possible through a formula or another method.  It would save me a ton of time rather than having to go into each cell in Column C to manually compute and enter this data.
                        B                                                                C
******************************************            *****************************************
   Event TimeStamp Local (UTC 0)                    Central Standard Time ( - 6 hrs)
******************************************            *****************************************
         10/24/2011 1:00:25 PM                                10/24/2011  07:00:25 AM
         10/25/2011 4:30:11 PM                                 10/25/2011 10:30:11 AM
         10/25/2011 2:50:01 PM                                 10/25/2011 08:50:01 AM
          "  "                                                             "  "
          "  "                                                             "  "
          "  "                                                             "  "

      Any suggestions or help is greatly appreciated.  Thanks.
Comment
Watch Question

Most Valuable Expert 2013

Commented:
If you have a valid time/date in B2 then you can subtract 6 hours with this formula in C2

=B2-6/24

regards, barry
Have you tried the formula (for cell c4):
=b4-.25

Worked for me!

Author

Commented:
Hey guys, both methods worked like a charm.  Is there a way to "hard code" into the spreadsheet so that I could possible select the cell in column  B and then click onto column c cell and it populate the output or will I have to go down the list and enter in the formula for each Cell in Column B?
Most Valuable Expert 2013
Commented:
You can easily "fill down". Put formula in first cell, e.g. C2, then select C2 and put the cursor on the bottom right of that cell until you see a black "+" - that's the "fill-handle" - double-click and the formula will automatically populate as far down as you have continuous data in the adjacent column (column C).

An alternative is to populate a specified range, even if there is not yet any data, e.g. to populate C2:C100 then put the formula in C2 and before and select C2. Then type the range required in the box above cell A1, i.e. type in C2:C100, press ENTER and then CTRL+D. That will populate that whole range.

In case you want to put the formula in to rows where there is not yet any data then you can make the formula return a blank until there is by changing to this

=IF(B2="","",B2-6/24)

regards, barry
Barry Beat me to it. You'll find that the fill handle trick, combined with the IF function, works very well. You can easily fill in dozens of cells with a quick drag, and the sheet looks clean.

Author

Commented:
Fabulous, thank you both VERY MUCH!