Convert DD/MM/YYYY HH:MM:SS from GMT to PST

[barry7377]

Hi,

I have a report where I need to convert the date from GMT to PST. The data in the cell is DD/MM/YYYY HH:MM:SS (example: 25/02/2013 14:00:00) and I need to convert the data into PST.

I have attempted the following formula without success:

=A1-TIME(8,0,0)

[Norie]

That should work.

Is it not returning the correct result?

[barry7377]

Hi Norie,

Unfortunately that does not work...I get #VALUE!

I think it's because the original data is both date and time and the formula only works with time?

[Norie]

It sounds more like the date isn't a date.

You can check for that with this.

=ISNUMBER(A1)

If that returns TRUE then A1 is a real date, if it returns FALSE it's not.

[barry7377]

It is returning FALSE and it's probably because of the way time is written in Europe (that's where my Excel data originate from). They write the date as DD/MM/YYYY instead of MM/DD/YYYY

[daddylonglegs]

You can subtract hours like that even with a date - if you get #VALUE! that probably means your date isn't recognised as a date (and is treated as text) that might happen if your default format is m/d/yy - can you convert the dates?

[barry7377]

I tested this by manually changing the date from DD/MM/YYYY and the formula worked. However I have over 950 entries...I can't possibly manually change the date format for all 950....

[Harribone]

You can use the format painter to save you the time.

[Norie]

Are they all in the one column?

If they are try going to Data>Text to columns... and either just click on Finish or on the 3rd step choose DMY for column date format.

If that doesn't work then you could try a formula to convert to real dates.

For example this.
[formula]
=DATE(MID(A1, 7,4), MID(A1, 4,2), LEFT(A1,2))+TIMEVALUE(RIGHT(A1, 8))
[/formual]
You can insert a column, enter this formula, copy it down, then copy and paste values over the original values and delete the column with the formula.