Date Time PDT

[ferde]

I have inherited an excel spreadsheet that has a column with both the date
and time in it followed by the abbreviation PDT ( pacific daylight time). I
would like to use a formula to change the column A entry to two columns
.....date and time.

Any help will be greatly appreciated.

Ex;
column A B C
6/4/2008 21:00 PDT change to 6/4/2008 21:00

 

[Bernard Liengme]

In both cells (B1 and C1) enter =A1
Now format B1 to show the date only; click cell; use CTRL+ 1 to open the
format dialog; select Date from the left side and from the right side of
dialog chose how you want to see the date
Format C1 to show only time
best wishes

 

[ward376]

What is the underlying value in column A? Format one of the cells as
general and post back. This may be as simple as referencing column A
in columns B and C and applying the format to display what you want.

Cliff Edwards

 

[ferde]

Thank you for the reply... I tried it but I think the PDT is messing things
up. I am trying to get rid of the PDT . I have A1 formatted as general
and B1 = A1. C1 = A1 also. I then formatted B1 as date and C1 as time
but no luck

EX:

6/5/2008 22:03 PDT 6/5/2008 22:03 PDT 6/5/2008 22:03 PDT

 

[ward376]

You may have text in column A.

Try this formula in column B:
=VALUE(LEFT(A2,FIND(" ",A2,1)))

and this one in column C:
=VALUE(MID(A2,(FIND(" ",A2,1))+1,FIND(" ",A2,1)-4))

then format column B as the date format you want and column c as the
time format you want.

Cliff Edwards

 

[ferde]

Thank you so much.... works Perfect

 

[ward376]

Actually, that time formula just 'happens' to work with your example.

It's logic is faulty... use this formula for column C, it's more
robust:

=VALUE(MID(A2,(FIND(" ",A2,1))+1,(LEN(TRIM(A2)))-(FIND(" ",A2,1)+4)))

Cliff Edwards

 

[ward376]

You're welcome.

Cliff Edwards

 

[ShaneDevenshire]

Hi Ferde,

If its text then highlight all the cells and choose Data, Text to columns,
Choose Delimited, click Next, choose Space, click Finish.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver and
help search for life beyond earth.