Home » Forums » Open Source Learning Center » Question and Answer

Dynamic Date Cells

Submitted by billfear on 10 November, 2009 - 06:46

Hi All

If someone out there could point me in the right direction it would be greatly appreciated!

Scenario:

I want to create a dynamic list of all of a particular day of the week (sunday in this case) referencing one cell containing the year.

For example cell A1 contains 2010 (next year)

The dates returned could be in A2, A3, A4.............etc

I would like the list to display in the format:

3/1

10/1

17/1

24/1

31/1

7/2

14/2

etc...etc

Any help is appreciated...Thanks

AttachmentSize
All-Sundays-In-the-year.xls21 KB
‹ Excel Workbooks Excel 2007 sheet taking long time to open ›
»
Nick's picture

Date cells

Submitted by Nick on 10 November, 2009 - 10:02.

Hi, I have attached a solution to your post that allows you to enter any year.

It works as follows:

  1. first we calculate the first day of the year
  2. then we calculate the weekday of the first day of the year
  3. If the weekday is 7, then it's a sunday, and we can stop there.
  4. If it's not 7, we need to add days to it until it reaches 7, at which point it's a sunday.

Nick

 

»

Date cells

Submitted by billfear on 10 November, 2009 - 16:29.

Hi Nick

So simple! My train of thought led me to more complex solutions.

A lesson to be learnt!

Many thanks for you help.

Bill

»