Repeat every month, ever year

[Michel_Rickhaus]

Hi y’all

I am wondering how you would go on about the following use case:
I have an app that allows to select a specific task from a list, tasks like lets say “water my plants” and then schedule it for any future date using a date input field.
I want the user to have a choice to repeat that tasks by choosing:
Start_Date: (Date)
Repeat_Every: (Number)
Repeat_Type: (Enum: Day, Week, Month, Year)
Repeat_For: (Number)
Repeat_For_Type: (Enum: Day, Week, Month, Year)

So that would spell out to something like repeat task X every 3rd day for 2 years or repeat task X every 2 months for 10 years) or whatever

I follow largely @Steve 's excellent tutorial on adding rows for a data range (works beautifully) but the repeating thing is getting to me.

As far as I understand I’d modify his Action 2 (Advance Date) here: IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + 1)) with a different integer I’d calculate based on the user input. That would take care of the frequency of the tasks.

Simply computing the end date based on the users input would also work nicely I think.

My main problem is: how would I know, how many days (or months etc) to ask, i.e. which integer:
Scheduling every 31 days wouldnt be the same as every month for instance.
So if someone want to schedule teh task to occur every two months it should remain on the same day (always on the X day of the month, but the amoutn of days in between wouldnt be constant, so I cant just add X days to the date)

Is this possible? Any help would be GREATLY appreciated!

Cheers
Michel

[Steve]

Michel_Rickhaus:

My main problem is: how would I know, how many days (or months etc) to ask, i.e. which integer:
Scheduling every 31 days wouldnt be the same as every month for instance.
So if someone want to schedule teh task to occur every two months it should remain on the same day (always on the X day of the month, but the amoutn of days in between wouldnt be constant, so I cant just add X days to the date)

Yeah, calendar math is exceptionally difficult.

To skip to this date 3 months in the future:

(EOMONTH(TODAY(), (0 + 3 - 1)) + DAY(TODAY()))

There is no easy way to compute something like “the first Tuesday of the month”.

See also:

help.appsheet.com

EOMONTH()

Date of last day of a month from Date or DateTime

[Michel_Rickhaus]

Hmmm…
Maybe something to call a webhook then to get the respective dates
Not sure if that exists or whether to skip the monthly calculations entirely and just have them say: on the xteenth of every x month (or similar)

[Steve]

Michel_Rickhaus:

Maybe something to call a webhook then to get the respective dates

Unfortunately, while you can call out with a web hook, you can’t retrieve a result.

Michel_Rickhaus:

or whether to skip the monthly calculations entirely and just have them say: on the xteenth of every x month (or similar)

That’s be easiest to implement, by far. Take care to handle dates near the end of the month that don’t occur in every month: not every month has a 29th, 30th, or 31st.

[Michel_Rickhaus]

oh wasn’t even aware that appsheet can’t handle returns…oh well

would be a great addition though, can’t think that there are no users that want to build some sort of task manager…

[Michel_Rickhaus]

@Steve
would you help me to work out how to implement this:

Michel_Rickhaus:

or whether to skip the monthly calculations entirely and just have them say: on the xteenth of every x month (or similar)

in your tutorial ?

Most is straightforward - as far as I get it, its just changing the integer here, right?
Add Date : IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + 1))

User input would be for simplicity:

• Frequency (Daily, Weekly, Monthly, Yearly)
• A frequency number N
• a start date
• an end date

This would translate to: repeat every Nth day/week/month/year starting from the defined start date

The corresponding integer needed for the Add date in your tutorial is then simply
*daily: N
weekly: 7N
yearly: 365N (and ignore the leap years for now)

Now for the months: The simplest is the same: start date defines the day of the month (say the 18th) and the frequency number defines “every nth month”. Im not entirely sure how to go on about this. The add date function wants an integer, which probably wouldn’t work because the time intervals are different from month to month

[Michel_Rickhaus · Accepted solution]

Here is what I think (with additional columns [Frequency] and [Number]) for the Add Date Action

SWITCH([Frequency], 
Daily, IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + [Number])), 
Weekly, IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + 7*[Number])), 
Monthly, IF(ISBLANK([Add Date]), [Start Date], IF(DAY([Start Date])>28, EOMONTH([Add Date], [Number]), EOMONTH([Add Date], [Number]-1) + DAY([Start Date]))), 
Yearly, IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + 365*[Number])), 
IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + [Number])))

[Matt_Peine]

Thanks for posting this, Michael!  Any chance you have the app you used this in available to look under the hood?  I'd love to be able to take a look so I can really understand how this works.  I'm too much of a beginner to figure it out just from your post.   If not, could you possibly elaborate on how to understand this and set it up?

[lucabizzotto]

Sorry @Michel_Rickhaus @Steve ,
can you explain to me what the [Number] is? And also how to calculate it?
I can't understand what you mean by "A frequency number N"
Thanks in advance!

[IjeomaO]

Thank you @Matt_Peine for initiating this post. This really helped me. 

Thank you @Steve  for always providing great expressions that showcase Appsheet's hidden capabilities.

Thank you @Michel_Rickhaus for the adaptive expression. This was the perfect example for me to be able to create an expression for where the user can loop and create pay periods for every pay schedule used in their company.

Please advise if you see any discrepancy with my expression.

For [Pay Period Beginning Date]:

SWITCH([Pay Schedule],

"Weekly", [Pay Period Beginning Date] + 7,

"Bi-Weekly", [Pay Period Beginning Date] + 14,

"Semi Weekly", IF([Pay Period Beginning Date] = EOMONTH([Pay Period Beginning Date], -1) + 1,

[Pay Period Beginning Date] + 15, [Pay Period Beginning Date] +16),

"Monthly", EOMONTH([Pay Period Beginning Date], 0) + Day([Pay Period Beginning Date]),

[Pay Period Beginning Date])

For [Pay Period End Date]:

SWITCH([Pay Schedule],

"Weekly", [Pay Period Beginning Date] + 6,

"Bi-Weekly", [Pay Period Beginning Date] + 13,

"Semi Weekly", IF([Pay Period Beginning Date] = EOMONTH([Pay Period Beginning Date], -1) + 1,

[Pay Period Beginning Date] + 14, [Pay Period Beginning Date] +15),

"Monthly", EOMONTH([Pay Period Beginning Date], 0),

[Pay Period Beginning Date])

For [Pay Date]:

SWITCH([Pay Schedule],

"Weekly",TEXT([Pay Period Beginning Date] + 11, "dddd, mmmm dd, yyyy"),

"Bi-Weekly", TEXT([Pay Period Beginning Date] + 19, "dddd, mmmm dd, yyyy"),

"Semi Weekly", IF([Pay Period Beginning Date] = EOMONTH([Pay Period Beginning Date], -1) + 1,

if(WEEKDAY(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 1, 0)) = 1,

TEXT(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 1, 1), "dddd, mmmm dd, yyyy"),
TEXT(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 1, 0), "dddd, mmmm dd, yyyy")),

if(WEEKDAY(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 1, 0)) = 1,

TEXT(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 15, 1), "dddd, mmmm dd, yyyy"),
TEXT(WORKDAY(EOMONTH([Pay Period Beginning Date], 0) + 15, 0) , "dddd, mmmm dd, yyyy"))),

"Monthly", TEXT(WORKDAY(EOMONTH([Pay Period Beginning Date], -1) + 1, 0),"dddd, mmmm dd, yyyy"),

[Pay Period Beginning Date])