Display day of week

[msdoni]

I need to display 2nd tuesday of every month for 5 years.
need to create a forumula to display 2nd tuesdays of every month

EXAMPLE (12 MONTHS AND 5 YEAR TABLE BELOW)

Cell should display the date of second tuesday for each month


2000 2001 2002 2003 2005
1
2
3
4
5
6
7
8
9
10
11
12

[DonkeyOte]

Assuming Years in B1:F1 and Months in A2:A13 then one possibility:

B2: =DATE(B$1,$A2,1)+CHOOSE(WEEKDAY(DATE(B$1,$A2,1),2),1,0,6,5,4,3,2)+7
applied across matrix B2:F13

[msdoni]

Thank you! For your help!

[msdoni]

I never used choose function. Can you please give a short description on how this works so that i can apply it to other situations like last working day of the month etc

[msdoni]

Need some advice with followint problems and how to calculate in excel

(a) Jessica wins the MA Lottery!!! The prize is $2,000,000 but is to be paid $100,000 per year for 20 years. How much money will the State of MA deposit in an account so that this annuity can be drawn out of the account. (The account will earn 7.5% annual.)

(b) Your uncle has bequeathed you $30,000, but due to your irresponsible nature
you will only be given $1500 per year for 20 years. How much he have to deposit into an account to allow these withdrawals from the account. The account earns 8.6% annual.

[DonkeyOte]

I never used choose function. Can you please give a short description on how this works so that i can apply it to other situations like last working day of the month etc

be sure to check out the help files...

If you do lots of date calcs you might be interested in activating the Analysis ToolPak Add-In (Tools -> Addins) ... eg last working day of the month:

=WORKDAY(EOMONTH(A1,0)+1,-1,holidays)
where A1 holds date
that in red being optional... (holidays being a named range containing public holiday dates)

Need some advice with followint problems and how to calculate in excel...

New question - new thread... also this sounds very much like homework....

[msdoni]

I am not askin for solution. I know i need to use fv or pmt function. But can you just clarify when and how i need to divide or multiply by 12.

rate/12 and number of period * 12

[msdoni]

I read helpfile for choose function. I see that you are choosing from one of the numbers listed in the function 1,0,6,5,4,3,2

Could you please explain the sequence of these numbers and how did you come up with them?

I would like to understand the concept and apply it to other situations like calculating last work day sales last monday sales etc

[DonkeyOte]

Could you please explain the sequence of these numbers and how did you come up with them?

Please Login or Register  to view this content.

Step 1 - take first day of month (red)

Step 2 - establish weekday of first day of month where Monday is day 1 and Sunday day 7 (blue)

Step 3 - based on Step 2 add n number of days to establish first Tuesday of the Month (green)
(ie first day of month is Monday and 1 to get to First Tuesday, if it's Tuesday add no days, if it's Wednesday add 6 days to get to first Tuesday and so on and so forth)

Step 4 - add 7 to the result to get the 2nd Tuesday


Regards your PV question - repeating myself - stick to one question per thread... moreover you've asked this question on another thread and have had responses.

The content of this thread should focus on the above calculation only.

[msdoni]

Apolegise for double posts and Thanks for breaking it down.

I will try to apply it to other scenarios of calculating sales.