EXCEL 2007: Computing UTC Time to My Time Zone
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Fabulous, thank you both VERY MUCH!
=B2-6/24
regards, barry