Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
debra adams
 
Posts: n/a
Default 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?
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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?

  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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?
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I calculate chronological age in excel Mike New Excel Discussion (Misc queries) 7 April 25th 23 11:45 AM
how to get week number in month in excel ? ikin Charts and Charting in Excel 2 January 16th 05 06:54 PM
Format an excel column as a date for a 5 day week for a year. dabenesch Excel Discussion (Misc queries) 1 December 30th 04 03:45 PM
USING THE DATE FORMAT IN EXCEL teach Excel Discussion (Misc queries) 3 December 15th 04 12:55 AM
Creating a Date Selector in Excel VBA? Mark Excel Discussion (Misc queries) 0 November 25th 04 11:59 PM


All times are GMT +1. The time now is 07:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"