Date reformatting question

[DateNightmare]

Hello all you Excel gurus out there. I have a problem that I am hopin
you can help me with.

I have a websurvey that collects information for me and then allows m
to export into a CSV file. The date the form is submitted comes throug
in the following format which seems to me to be a General formatted cel
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 thei
end so it is up to me and/or Excel to figure out a way to accomplis
this.

Thanks for any help

 

[Claus Busch]

Hi,

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

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

 

[joeu2004]

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)

 

[Claus Busch]

Hi,

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

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

 

[Claus Busch]

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

 

[joeu2004]

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.