Calculating days of week

[HBYardSale]

This should be a simple one, but can't find it in any of the help menus....

In column A, I have a list of dates, like 4-13-04.
In column B, I would like the day of the week to be displayed.

I found this, which returns a "1" for Sunday, "2" for Monday, etc., but can't
figure out how to get it to automatically display the name of the day.

=+WEEKDAY("4-13-04")

Many thanks in advance!

-- J

 

[Bob Phillips]

=TEXT(A1,"dddd")

where A1 is the date.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Anders S]

Assuming the list starts in A2,

In B2 enter "=A1" without the quotes. Format B2 as DDD or DDDD.

HTH
Anders Silven

 

[Nick Hodge]

J

Do you need a programming response?

Copy your original dates into column B but this time via
Format>Cells...>Number>custom type dddd

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

There is a WeekDayName function, but its a VBA function not a worksheet Function. I'm not sure how to call it in a worksheet , maybe someone else does

x = WeekDayName(Weekday(MyDate))

 

[JE McGimpsey]

One way:

B1: =A1

Format B1 using Format/Cells/Number/Custom dddd

 

[Tom Ogilvy]

=text(today(),"dddd")

returns a string like Monday

--
Regards,
Tom Ogilvy

There is a WeekDayName function, but its a VBA function not a worksheet

Function. I'm not sure how to call it in a worksheet , maybe someone else
does.

 

[Norman Harker]

Hi HBYardSale!

Replies show that you have various choices.

Three approaches with selection depending upon input and subsequent
needs:

Approach 1:
=A1
Format dddd or ddd

Would be used where you want to de able to use the underlying date
later especially where instead of a reference to a date in A1, you use
a calculated date: eg

=DATE(YEAR(A1),MONTH(B1),1)
Format dddd
Returns the 1st of the month and displays as a day of week. Allows you
to use date calculations on the date stored.

Approach 2:
=TEXT(A1,"dddd")
Returns the day of week of the date in A1.

You can't do subsequent calculations with this very easily (although
you still have the ability to use the date in A1 or the calculation
used to provide the date that is used). But it might be useful for
presentation purposes eg:

=UPPER(TEXT(A1,"dddd"))
Returns the day of week in upper case.

Approach 3:
=WEEKDAY(A1)
Format ddd or dddd
Returns the day of week as a number (Sun = 1, Mon = 2 etc) but
displays as a name.

We use this especially where we want to use the day number
subsequently. eg:

=IF(WEEKDAY(A1)=1,B1*1.5,B1)
Returns B1*1.5 if A1 is a Sunday but otherwise returns B1

Incidentally, the use of WEEKDAY relies on an error which made
1-Jan-1900 a Sunday when in fact it was a Monday. The error corrects
itself after 28-Feb-1900 because in the 1900 date system they inserted
a Leap Year day which didn't exist.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/