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

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

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

"debra adams" wrote:

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

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

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