With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Wednesday, January 9, 2013
yogi_Generate A Single Entry That Shows Multiple Dates Extracted From Specified Condition
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).
The Master sheet has a routine (on a single row) "Go To Work" with some kind of formula that indicates "every weekday".
I select a date range that encompasses 1 week (e.g. Monday 1/7/13 - Sunday 1/13/13).
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...
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:
There should be some kind of value to suggest "Every Thursday" in C5
I should then put a date in B1 and B2 (a range).
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).