Resource planner - Recurring events - Timeline independent (Work in progress)

[Ratatosk]

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
  
  

[Ratatosk]

//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)

)
)
)
)

[Ratatosk]

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)

[Ratatosk]

Empty3

[SkrOYC]

Ratatosk:

Needs checkpoint to find out actual week amount in the given year

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/"

[Ratatosk]

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!

[SkrOYC]

Ratatosk:

[monthValue]: Column with allowed enumlist values 01,02,03,04,05,06,07,08,09,10,11,12
Reason for “01”, instead of “1” is that “contains” can’t differentiate between “1” and other numbers containing “1” like “11,12,21” etc.

You could use IN() which is appropriate for lists instead of CONTAINS() that works best for strings/text

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

CONTAINS()

Does text contain fragment?

Anyway, I like to add 0 for consistency and better sorting, since some apps/systems also order poorly and do strange things like this:

• 1
• 10
• 11
• 12
• 2
• 3…

[Ratatosk]

Great!

[Steve]

Huh?

[Ratatosk]

Huh.

[Ratatosk]

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.

[Steve]

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.

[Ratatosk]

@Steve @Rafael_ANEIC-PY

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.

[Ami]

Well… i created the same thing, looks almost the same a few weeks ago!! Amazing

I built it as follows:

1. An event that happens on a weekly basis ( sunday to saturday )
2. A schduled event on a specific date with the ability to add multiple months.

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.

[Ami]

[Steve]

Ah! Thank you for the explanation! I’ll try to come back to this and offer some thoughts.

[Chris_Jeal]

Hi guys,

I wonder if some of the functionality in @GreenFlux app may work well with resource planning?

SAMPLE APP: Crazy Calendar Hack Tips & Tricks 💡

Here’s a new take on calendar views, based on some Virtual Column weirdness: [2020-12-22 15.38.59] The sample app uses a public holiday calendar, and a WeekNum table with just two sheet columns: https://www.appsheet.com/samples/Built-from-your-app-spec-?appGuidString=4fe2fb91-feca-4a6e-bee3-3ab... [Screen Shot 2020-12-22 at 8.56.48 AM] [Screen Shot 2020-12-22 at 8.57.31 AM] Here’s a quick overview of how it works: I used a public/read-only calendar so I could post it as a sample. Bu…

[Ratatosk]

@Chris_Jeal Interesting idea. I’ll look into it after I finish the recurring events formulas.

[Rafael_ANEIC-PY]

@Steve must be the most expressive person in this forum, I read his

Steve:

Huh?

And i knew he meant

Steve:

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.

Also, I share the sentiment hehe

[Steve]

None this is tested.

Day of Week

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]
  )
)

Nth Week of Month

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")
        )
      )
    )
  )
)

Week of Year

OR(
  ISBLANK([weekValue]),
  IN(RIGHT(("0" & ISOWEEKNUM([vToday])), 2), [weekValue])
)

If [weekNumber] instead:

OR(
  ISBLANK([weekNumber]),
  IN(ISOWEEKNUM([vToday]), [weekNumber])
)

Odd/Even Week of Year

OR(
  ISBLANK([oddEven]),
  ((MOD(ISOWEEKNUM(vToday]), 2) = IF(("Odd" = [oddEven]), 1, 0))
)

Month of Year

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]
  )
)