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

D

debra adams

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

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
 
Top