Choose function

[hhgoh]

Hi all,


I am not able to figure out the no series of "2,1,7,6,5,4,3" which shown in the choose function.


Can someone help ?


TIA.


When is Labor Day (US) in 2010?


Labor day (the US variant) is celebrated on first Monday of every September. It occurs on Sep 6th in 2010.

Using excel date formulas, you can easily find out the labor day’s date for any given year.

Here is the formula I have used:

=DATE(2010,9,CHOOSE(WEEKDAY(DATE(2010,9,1)),2,1,7,6,5,4,3))

How this formula works?

 

[Hui]

Weekday (Date(2010,9,1)) returns the day of the week, sun = 1 to Sat = 7

and Choose uses this to tell what date the first Monday is

eg: If the first day is a Sunday you need the second of the month (First Monday)

If the first day is a Wednesday (weekday 4) you need the sixth of the month (

So Choose chooses the Day No associated with the Weekday from the list 2,1,7,6,5,4,3

 

[hhgoh]

Hui, thank you for your prompt reply,but how does the list '2,1,7,6,5,4,3' being workout ?


=(WEEKDAY(DATE(2010,9,1))) return the value of '4' and choose function pick up '6' as the value. I still in the dark. sorry about that.

 

[Hui]

You are after the day number to put into the Weekday(Date()) part of the equation for the first Monday of Sept

You don't know what Date/Day that is, but you can work it out as an offset from the 1st of the month.

So the Weekday(Date(2010,9,1)) part of the eqn returns the day No of the 1st of Sept, 2010

This is then used to choose the corresponding Date of the First Monday


Ie if the 1st of the month is a Sunday 9Day No 1), The date of the first Monday will be 2 see the table below

If the first of the Month is a Tuesday (day No 3), the first Monday will be the 7th

etc


Day Weekday Corresponding Next Monday

Sun 1 2

Mon 2 1

Tue 3 7

Wed 4 6

Thu 5 5

Fri 6 4

Sat 7 3


Hope that helps

 

[hhgoh]

exactly, I got it now, thank you for your patient, Hui. I have another question, I think i will open it as a new q.