Re: Calculating days of week
From: Norman Harker (njharker_at_optusnet.com.au)
Date: 04/19/04
- Next message: Angie: "Build macro to draw lines between multiple points"
- Previous message: SuperJas: "RE: Switching from InputBox to UserForm?"
- In reply to: HBYardSale: "Calculating days of week"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 13:21:49 +1000
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 njharker@optusnet.com.au 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/
- Next message: Angie: "Build macro to draw lines between multiple points"
- Previous message: SuperJas: "RE: Switching from InputBox to UserForm?"
- In reply to: HBYardSale: "Calculating days of week"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
