This is a live document that will expand over time.
For now it will most likely contain some faults!
I would appreciate any help making this more fleshed out, formula effective and accurate over time.
Initial description
Building blocks for a flexible resource planner. To be used in a Slice formula.
Use Show formulas to โlive hideโ columns that cannot be edited if another column has value.
Important columns
[dateFiltration] Enum to select if you want to filter from Month, Weeks, or NWeeks
[dayValue]: Column with allowed enumlist values 1,2,3,4,5,6,7 (Weekdays: Sunday, Monday โฆ Friday) Will be fixed later so Monday is first day.
[weekValue]: Column with allowed enumlist values 1-53 (Weeks of year)
Needs checkpoint to find out actual week amount in the given year
[monthValue]: Column with allowed enumlist values 1,2,3,4,5,6,7,8,9,10,11,12 (Months of year)
[vToday]: Virtual column to test today() or other specific dates
[oddEven]: Column with enum values โOddโ and โEvenโ (Odd/Even weeks)
[nWeekOfMonth]: Column with enumlist values โFirstโ, โSecondโ, โThirdโ, โFourthโ, โLastโ. (Of Month)
[everyNWeekStart] Filter from week number.
[everyNWeek] Interval between weeks. (Minimum 3)
[vEveryNWeekResult] Virtual column with generated list of Nweeks
//SLICED BY MONDAY
OR(
AND( [dateFiltration]="Weeks",
IN(ISOWEEKNUM([vToday]),[weekValue]),
IN(2,[dayValue])
),
AND( [dateFiltration]="Interval",
IN(ISOWEEKNUM([vToday]),[vEveryNWeekResult]),
IN(2,[dayValue])
),
AND( [dateFiltration]="Months", IN(2,[dayValue]), IN(MONTH(EOWEEK([vToday])-5), [monthValue]),
OR( AND(ISBLANK([oddEven]), ISBLANK([nWeekOfMonth])),
AND(
MOD(ISOWEEKNUM([vToday]),2)=1,
[oddEven]="Odd"
),
AND(
MOD(ISOWEEKNUM([vToday]),2)=0,
[oddEven]="Even"
),
AND( IN("First", [nWeekOfMonth]),
DAY(EOWEEK([vToday])-5)<=7
),
AND( IN("Second", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday])-5)<=14,DAY(EOWEEK([vToday])-5)>=8)
),
AND( IN("Third", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday])-5)<=21,DAY(EOWEEK([vToday])-5)>=15)
),
AND( IN("Fourth", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday])-5)<=28,DAY(EOWEEK([vToday])-5)>=22)
),
AND(
IN("Last", [nWeekOfMonth]),
IN(2, [dayValue]),
DATE(EOWEEK([vToday])-5)=
EOMONTH(DATE(EOWEEK([vToday])-5),0)+1- WEEKDAY(EOMONTH(DATE(EOWEEK([vToday])-5),0)+1-2)
)
)
)
)
Other formulas:
[vEveryNWeekResult]
List ( [everyNWeekStart]) +
Ifs([AmountIntervals]>1, LIST([everyNWeekStart]+[everyNWeek]))+
Ifs([AmountIntervals]>2, LIST([everyNWeekStart]+[everyNWeek]*2))+
Ifs([AmountIntervals]>3, LIST([everyNWeekStart]+[everyNWeek]*3))+
Ifs([AmountIntervals]>4, LIST([everyNWeekStart]+[everyNWeek]*4))+
Ifs([AmountIntervals]>5, LIST([everyNWeekStart]+[everyNWeek]*5))+
Ifs([AmountIntervals]>6, LIST([everyNWeekStart]+[everyNWeek]*6))+
Ifs([AmountIntervals]>7, LIST([everyNWeekStart]+[everyNWeek]*7))+
Ifs([AmountIntervals]>8, LIST([everyNWeekStart]+[everyNWeek]*8))+
Ifs([AmountIntervals]>9, LIST([everyNWeekStart]+[everyNWeek]*9))+
Ifs([AmountIntervals]>10, LIST([everyNWeekStart]+[everyNWeek]*10))+
Ifs([AmountIntervals]>11, LIST([everyNWeekStart]+[everyNWeek]*11))+
Ifs([AmountIntervals]>12, LIST([everyNWeekStart]+[everyNWeek]*12))+
Ifs([AmountIntervals]>13, LIST([everyNWeekStart]+[everyNWeek]*13))+
Ifs([AmountIntervals]>14, LIST([everyNWeekStart]+[everyNWeek]*14))+
Ifs([AmountIntervals]>15, LIST([everyNWeekStart]+[everyNWeek]*15))+
Ifs([AmountIntervals]>16, LIST([everyNWeekStart]+[everyNWeek]*16))+
Ifs([AmountIntervals]>17, LIST([everyNWeekStart]+[everyNWeek]*17))+
Ifs([AmountIntervals]>18, LIST([everyNWeekStart]+[everyNWeek]*18))
[AmountIntervals]
Floor(((ISOWEEKNUM(
EOMONTH(
CONCATENATE(
"12/1/",
YEAR([vTODAY])
),
0
)
)-[everyNWeekStart])/[everyNWeek])+1)
Empty3
Maybe this?
ISOWEEKNUM(
EOMONTH(
CONCATENATE(
"12/1/", /*The order of this numbers may vary on locale I think*/
YEAR(TODAY())
),
0
)
)
You could change TODAY()
with any date, a [Date]
column for example or change the whole YEAR(TODAY())
with a number column where you could point the year.
For people in the US (replacing ISOWEEKNUM()
with WEEKNUM()
), this may not work since the last week of december doesnโt necessarily contains the last day of the year
Edit: You could also remove the EOMONTH()
and just replace the "12/1/"
with "12/31/"
ISOWEEKNUM(
EOMONTH(
CONCATENATE(
โ12/1/โ, /The order of this numbers may vary on locale I think/
YEAR(TODAY())
),
0
)
)
@SkrOYC Very cool! This was very useful to learn!
You could use IN()
which is appropriate for lists instead of CONTAINS()
that works best for strings/text
Anyway, I like to add 0 for consistency and better sorting, since some apps/systems also order poorly and do strange things like this:
Great!
Huh?
Huh.
Hi Steve, I think I understand your elegant and elaborate comment now.
If you think this is the wrong place to put a work in progress, I ask you kindly to guide me to the right place to add it.
Actually, I canโt make sense of where youโre going with this topic. I thought I might be able to help, but I donโt understand what youโre trying to accomplish.
I see. Let me try to explain my reasons for A. putting it up here, and B. my personal reasons for making it.
A.:
Searching for help on the topic recurring events didnโt yield much results. Everything I found was based on putting things in a timeline from date A to date B. I need it to work universally without regarding date, until a later comparison in a slice.
Hope this helps others in the future to be not so alone on figuring things out.
I had to do some excel formula hunting and translate that into appsheet, and also create some of the logic from scratch. And I am not a math person (yet!)
B:
Iโm making my own resource planner with recurring events based on services, and people/groups responsible for executing these services. I want the recurring events to be easily changed, and to go on forever, until the record is deleted/moved to a separate archive.
Slices will be used to show different time representations of the data. Today, tomorrow, this week, etc.
Wellโฆ i created the same thing, looks almost the same a few weeks ago!! Amazing
I built it as follows:
I then run a bot every hour that checks if the task is between current time and current date, if the criterias match it adds a row to a new table.
Ah! Thank you for the explanation! Iโll try to come back to this and offer some thoughts.
Hi guys,
I wonder if some of the functionality in @GreenFlux app may work well with resource planning?
@Chris_Jeal Interesting idea. Iโll look into it after I finish the recurring events formulas.
@Steve must be the most expressive person in this forum, I read his
And i knew he meant
Also, I share the sentiment hehe
None this is tested.
OR(
ISBLANK([dayValue]),
IN(RIGHT(("0" & WEEKDAY([vToday])), 2), [dayValue])
)
If [dayNumber]
instead:
OR(
ISBLANK([dayNumber]),
IN(WEEKDAY([vToday]), [dayNumber])
)
If [dayName]
instead:
OR(
ISBLANK([dayName]),
IN(
INDEX(
LIST("Sun", "Mon", "Tue", "Wed", "Thu", "Sat", "Sun"),
WEEKDAY([vToday])
),
[dayValue]
)
)
OR(
ISBLANK([nWeekOfMonth]),
ISNOTBLANK(
INTERSECT(
[nWeekOfMonth],
(
LIST(
IFS(
([vToday] < (EOMONTH([vToday], -1] - WEEKDAY(EOMONTH([vToday], -1]) + 2 + 7)),
"First",
([vToday] < (EOMONTH([vToday], -1] - WEEKDAY(EOMONTH([vToday], -1]) + 2 + 14)),
"Second",
([vToday] < (EOMONTH([vToday], -1] - WEEKDAY(EOMONTH([vToday], -1]) + 2 + 21)),
"Third",
([vToday] < (EOMONTH([vToday], -1] - WEEKDAY(EOMONTH([vToday], -1]) + 2 + 28)),
"Fourth",
true,
"Fifth"
)
)
+ IFS(
([vToday] > (EOMONTH([vToday], 0] - WEEKDAY(EOMONTH([vToday], 0]) + 1)),
LIST("Last")
)
)
)
)
)
OR(
ISBLANK([weekValue]),
IN(RIGHT(("0" & ISOWEEKNUM([vToday])), 2), [weekValue])
)
If [weekNumber]
instead:
OR(
ISBLANK([weekNumber]),
IN(ISOWEEKNUM([vToday]), [weekNumber])
)
OR(
ISBLANK([oddEven]),
((MOD(ISOWEEKNUM(vToday]), 2) = IF(("Odd" = [oddEven]), 1, 0))
)
OR(
ISBLANK([monthValue]),
IN(RIGHT(("0" & MONTH(vToday])), 2), [monthValue])
)
If [monthNumber]
instead:
OR(
ISBLANK([monthNumber]),
IN(MONTH([vToday]), [monthNumber])
)
If [monthName]
instead:
OR(
ISBLANK([monthName]),
IN(
INDEX(
LIST("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"),
MONTH([vToday])
),
[monthName]
)
)