Day of week from Date 1

[attrofy]

Column A2 has the date, Column B1 needs to report the Day of the week (i.e. Monday, etc). I know the cell can be formated to include the date, but I need to pull the day out in a mail merge with Word. So if there is a way within Word to do this, this step can be avoided. But then I need to know how to do this in Word.

Thanks for the help.

 

[attrofy]

Oops - never mind, I got it -just had to change the search criteria in the help files. For anyone else looking to display the Day of the week:

in cell B2
=TEXT(A2,"dddd")

 

[Rofeu]

Hi,

give the TEXT formula a try.

Cheers,

Roel

 

[AnotherHiggins]

attrofy,

[attn]NOTE[/attn]: Using the TEXT formula will convert your date to a.... You guessed it - TEXT! This might be what you want to do. But consider the following:

You can simply enter [COLOR=blue white]=A2[/color], then go to Format > Cells > Number > Custom > dddd. This does not change the date stored in the cell, it only changes how it is displayed. See faq68-5827 for more details on how Excel stores dates and times.

Whether using the TEXT formula or just changing the display of the cell, the following applies:

d will display the day of the month
[tab]Example: Today would be 29, Friday would be 1

dd will display the day of the month with two characters
[tab]Example: Today would be 29, Friday would be 01

ddd will display the abbreviated weekday in three characters
[tab]Example: Today would be Tue

dddd will display the weekday
[tab]Example: Today would be Tuesday

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Just to add to what John stated...

When you convert a date to text, it only sorts as text.
[tt]
Fri
Mon
Sat
Sun
Thu
Tue
Wed
[/tt]

If you use FORMATTING, as John suggested, it will continue to sort in proper date sequence.


Skip,

_[red][/red]

 

[attrofy]

Thanks for the input all. Everything you stated is correct. For my case, I was trying to do something very simple - print a page with the date sequentialy from today through this date next year - accounting for all monthly variations. A mail-merge seemed the most logical. In my particular case, the date printing as text was actually prefered. I had initially changed the date display in Excel, but then when merged into Word, the format reverted back to standard numeric formatting (i.e. 8/30/06).

My not so elegant solution was to break the date info down (with the TEXT function) for B1=Day of week C1=Month, D1=Day of month, E1=Year. Then I merged each field into its appropriate location in the Word document. As the data was now text, it imported the month spelled out, instead of the numeric equivelant.

Thanks to all for the suggestions. Hopefully someone else will find this thread answers a few questions.

 

[Rofeu]

Hi,

you can do that within the TEXT formula, i.e.:

=TEXT(Date,"DDDD MM, DD, YYYY")

say the date is 31/08/2006:

M = 8
MM = 08
MMM = Aug
MMMM = August

similarly with D for the day.

Cheers,

Roel

 

[attrofy]

Ahhhh....that is the elegance I was looking for - thanks Roel. Its always the simplest way that is most logical and elegant.

 

[Rofeu]

Glad to have been of help. Thanx for the star.

Cheers,

Roel