yogi_Generate A Single Entry That Shows Multiple Dates Extracted From Specified Condition

                                          Google Spreadsheet   Post  #962

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 09, 2013

user Solikos said:(http://productforums.google.com/forum/?zx=cj4ac0rnxhk2#!mydiscussions/docs/W-nWz5uS4qY)
Single Value To Represent Reoccurring/Multiple Dates

Chrome / Windows 7

2 Questions, but first an explanation of the project:

I'm trying to create a list of routines and then (referencing that list) generate another list of the applicable routines within a provided time-frame.

I anticipate using a "Master" sheet that contains a list of all my routines. The master sheet would ideally contain a single row for each routine. So instead of using a start date, it would use something like a cron expression to explain when it needs to be generated.

The Master sheet would feed (or be filtered) into a "Plan" sheet based on a date range. However, sometimes a single routine may need to be created multiple times in the "Plan" sheet (assuming the routine occurs at least two times in the given time-frame).

For example:

1. The Master sheet has a routine (on a single row) "Go To Work" with some kind of formula that indicates "every weekday".
2. I select a date range that encompasses 1 week (e.g. Monday 1/7/13 - Sunday 1/13/13).
3. The Plan sheet is populated with 5 rows of the "Go To Work" routine, each with a unique execution/start date (e.g. 1/7/13, 1/8/13, 1/9/13, 1/10/13, 1/11/13).

Question #1: Is it possible to essentially mimic a cron expression in a spreadsheet (to have 1 value define multiple, reoccurring or infinite date values? If so, are there a small number of formulas for this or a good place for me to reference?

Question #2: Assuming multiple dates from a single value is possible, how would I manipulate this "cron-like" value in a filter to appear multiple times (or none if not applicable) and populate the respective dates? I assume this is some kind of arrayformula filter, but not sure what type of value I'd even be dealing with here...

Here is a public access link to an example: https://docs.google.com/spreadsheet/ccc?key=0Al5BvbCCY8mIdEpjeE11Q0Z6R01tZTVUc25tbU5ScWc#gid=0

• yellow fields are edited by the user
• blue field should be the cron type of value (column B is the written explanation, but isn't needed on the "Plan" tab)

And if this is just impossible, what about alternatives? Perhaps a column for "First", "Frequency", "Last"? Other ideas?

Thanks in advance!

---

Maybe that was overwhelming. Let's say it this way:

1. There should be some kind of value to suggest "Every Thursday" in C5
2. I should then put a date in B1 and B2 (a range).
3. I would then like to have the filter generate a list of dates that match the value in C5 WITHIN the range (B1 & B2)... So I would be provided with a list of every Thursday in date format between B1 and B2 (if any).

Possible? Is there such a value that will suggest "Every Thursday" and other more complex suggestions (e.g. The 3rd Friday of January & July).

------------------------------------------------------------------------------------------

following is a solution to the first part of your problem ... 'Every Thursday'