Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time PDT
You're welcome.
Cliff Edwards |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |