Quote:
Originally Posted by DateNightmare
Hello all you Excel gurus out there. I have a problem that I am hoping you can help me with.
I have a websurvey that collects information for me and then allows me to export into a CSV file. The date the form is submitted comes through in the following format which seems to me to be a General formatted cell and not a proper Date/Time cell. It comes through as:
2012-11-02 16:02:19 Etc/GMT
The problem is that I need these times to be in Pacific Time, not GMT. I checked with the websurvey provider and they cannot change it on their end so it is up to me and/or Excel to figure out a way to accomplish this.
Thanks for any help!
|
One way would be putting
=VALUE(LEFT(A1,19)) in a blank column (alter A1 to the relevant cell containing the current data) and copy down. Then custom format to YYYY/MM/DD HH:MM:SS or YYYY/DD/MM HH:MM:SS depending on your geographical location and preference for displaying dates.
Does that help?
EDIT: Sorry, I completely forgot aout the GMT to Pacific Time part.. You can do that by just subtracting the relevant number of hours from the value produced. So for example =VALUE(LEFT(A1,19))-0.291666666666667 (to subtract 7 hours).