How to convert a date field of "Day" and "Date" 1

[wec43wec]

How do I convert the date (consisting of the day and date) into two separate columns as indicated in my example below:

When I copy the date(Fri, March 21, 2008) as a paste special (values only) into another cell thinking that this would result in a text field, I then used a "+left(A1,3)" which resulted in a number of 3962 and not Fri.

Example:

Col A1 = Fri, March 21, 2008

Objective is the following:

Col B1 = Fri

Col C1 = March 21, 2008

 

[GlennUK]

What are the contents of cell A1? And please give the cell contents, and not just what the formatting does ( a cell contents may be a date 25/03/2008 but be formatted to show Tue, March 25 2008 ... I want the contents and NOT what the formatted cell shows ).

Why do you want "Fri" exactly? What are you going to use it for?

Please read:
Why do Dates and Times seem to be so much trouble? faq68-5827

Note: 21st March is a serial number of 39528.

Useful function... TEXT, which can be used like =TEXT(cellref,"ddd") to give "Fri" and so on, when the cellref is a date serial number.





Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Hi wec43wec,

Why not use =A1 for both B1 and C1, then format B1 as 'ddd' and C1 as 'mmm d, yyyy'?

Cheers

[MS MVP - Word]

 

[wec43wec]

Macropod - your solution works very well and is just what I was looking for - Thanks and a Star for you !

GlenUK - you asked a number of questions. My response are as follows:

Q - What are the contents of cell A1?
A - The date of March 21, 2008 was typed in A1 with a format using the first selection in the "Date" option under cell / format which shows - *Wednesday, March 21, 2001.

Q - Why do you want "Fri" exactly? What are you going to use it for?
A - The objective was to use the data in a pivot table and be able to average data by the "day of the week" - Mon, Tue, Wed, etc. The data I have will only show the Month/day/year (ex March 21, 2008). I needed the day next to the date.

I do appreciate your response, but macropod proved a better solution.

 

[GlennUK]

Macropod's solution is fine for reporting purposes, but I think that you'll need =TEXT(cellref,"ddd") as I suggested, if you are wanting to summarise by day of week in a PivotTable.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Hi Glenn,

One could of course use =WEEKDAY(A1) in B1 and format as 'ddd'.

Cheers

[MS MVP - Word]

 

[GlennUK]

Hi Macropod,

yes, of course you could do that, but some people might find it confusing to format a number ( 1 to 7 ) with a date type format. It just happens to be luck that 1st Jan 1900 ( the date that number 1 would represent, were it a date serial number ) happens to be a Sunday.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Hi Glenn,

It just happens to be luck that 1st Jan 1900 ... happens to be a Sunday.

Actually, it wasn't - it was a Monday. I know Excel says otherwise, but Excel is wrong, just as Excel wrongly treats 1900 as a leap year.

Cheers

[MS MVP - Word]

 

[GlennUK]

Yes, I know it was a Monday, Excel says Sunday. Excel is wrong on purpose :

The reason for this error is compatibility. In the early days of personal computing, Lotus 1-2-3 was the most popular software available. Lotus programmers made the leap year mistake, and Microsoft programmers reproduced it so they could use the same date serial number scheme as 1-2-3.

Just one of Excel's oddities.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.