Excel: Is there a way to calculate the date as week of month?

[Guest]

I have need to show dates as follows: 1/1/05 = 1st Saturday of January...any
ideas out there?

 

[Guest]

You can't format the dates as 2nd Sunday of January 2005 but there are ways
of using formulas to get nth day etc

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoW


Regards,

Peo Sjoblom

 

[Jason Morin]

As Peo mentioned, I doubt you can format the dates as
such. You can, however, convert it to a text string in
another cell. Assuming your dates are in column A,
starting in A1:

1. Press Ctrl+F3 and create the name "dow" (no quotes).
In the "Refers To:" box put:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($A1),MONTH
($A1),1)&":"&$A1)))=WEEKDAY($A1)))

Watch the wrap.

2. Now in row 1 of your worksheet put:

=dow&CHOOSE(dow,"st","nd","rd","th","th")&" "&TEXT
(A1,"dddd")&" of "&TEXT(A1,"mmmm")

HTH
Jason
Atlanta, GA