Date reformatting question
 

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).

Date reformatting question
 

Hi,

Am Mon, 5 Nov 2012 18:13:37 +0000 schrieb DateNightma

2012-11-02 16:02:19 Etc/GMT

The problem is that I need these times to be in Pacific Time, not GMT.

your date and time in A1:

=LEFT(A1,19)-TIME(8,,)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Date reformatting question
 

"DateNightmare" wrote:
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

The problem is made complicated by Daylight Savings Time. GMT is not
adjusted for DST.

I believe there is a way to take DST into account automagically by using a
system function in VBA. Do you want to go in that direction?

Alternatively, you could have a cell (e.g. D1) with 0 (not DST) or 1 (DST),
then use the following formula:

=LEFT(A1,19)-TIME(8-D1,0,0)

But that assume that date is always 10 characters and the time is always 8
characters. Probably true. But more generally:

=LEFT(A1,FIND(" ",A1,1+FIND(" ",A1)))-TIME(8-D1,0,0)

Date reformatting question
 

Hi,

Am Mon, 5 Nov 2012 18:13:37 +0000 schrieb DateNightma

2012-11-02 16:02:19 Etc/GMT

i forgot the DST. But if you enter a constant in D1 every time will be
changed when you change D1.
Your date and time in A1 then try:
=LEFT(A1,19)-TIME(9-(AND(--LEFT(A1,10)=DATE(LEFT(A1,4),3,1)+13-(WEEKDAY(DATE(LEFT(A1,4),3,1)-1)-1),--LEFT(A1,10)<=DATE(LEFT(A1,4),10,31)-(WEEKDAY(DATE(LEFT(A1,4),10,31))-1)+7)),,)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Date reformatting question
 

Hi again,

Am Tue, 6 Nov 2012 09:13:36 +0100 schrieb Claus Busch:

Your date and time in A1 then try:
=LEFT(A1,19)-TIME(9-(AND(--LEFT(A1,10)=DATE(LEFT(A1,4),3,1)+13-(WEEKDAY(DATE(LEFT(A1,4),3,1)-1)-1),--LEFT(A1,10)<=DATE(LEFT(A1,4),10,31)-(WEEKDAY(DATE(LEFT(A1,4),10,31))-1)+7)),,)
^ ^^^^^^

sorry, i have a typo in the formula. Try:
=LEFT(A1,19)-TIME(8-(AND(--LEFT(A1,10)=DATE(LEFT(A1,4),3,1)+13-(WEEKDAY(DATE(LEFT(A1,4),3,1)-1)-1),--LEFT(A1,10)<=DATE(LEFT(A1,4),10,31)-(WEEKDAY(DATE(LEFT(A1,4),10,31))-1)+7)),,)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Date reformatting question
 

Clarification.... I wrote:
The problem is made complicated by Daylight Savings Time.
GMT is not adjusted for DST.
[....]
Alternatively, you could have a cell (e.g. D1) with 0
(not DST) or 1 (DST), then use the following formula:
=LEFT(A1,19)-TIME(8-D1,0,0)
[...or...]
=LEFT(A1,FIND(" ",A1,1+FIND(" ",A1)))-TIME(8-D1,0,0)

You should then copy the converted times and paste-special-value so that you
have constant times again.