Date Time PDT
 

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

Date Time PDT
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ferde" wrote in message
...
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

Date Time PDT
 

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

Date Time PDT
 

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




"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ferde" wrote in message
...
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

Date Time PDT
 

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

Date Time PDT
 

Thank you so much.... works Perfect :)

"ward376" wrote:

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

Date Time PDT
 

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

Date Time PDT
 

You're welcome.

Cliff Edwards

Date Time PDT
 

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.

"ferde" wrote:

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




"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ferde" wrote in message
...
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