ExcelBanter - Calculating days of week

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Calculating days of week (https://www.excelbanter.com/excel-programming/295479-calculating-days-week.html)

Calculating days of week

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

Calculating days of week

=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)

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

Calculating days of week

Assuming the list starts in A2,

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

HTH
Anders Silven

"HBYardSale" skrev i meddelandet ...
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

Calculating days of week

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))

Calculating days of week

=text(today(),"dddd")

returns a string like Monday

--
Regards,
Tom Ogilvy

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

Calculating days of week

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

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/