Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Quote:
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). Last edited by Spencer101 : November 5th 12 at 07:15 PM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retain custom date format "text" when pasting or reformatting data | Excel Discussion (Misc queries) | |||
Excel reformatting a date axis to January | Charts and Charting in Excel | |||
Stopping Excel from reformatting data as a date? | Excel Discussion (Misc queries) | |||
US date needs reformatting but not recognised by my UK computer | Excel Programming | |||
Reformatting question | Excel Programming |