Hello Everyone ! I’m creating an App for a Veterinary, to handle hospitalizations. I’m a french native speaker (from Belgium) and i did build the App in French. My spreadsheets are set up in french, but i keep the formulas in English of course. Time Format is Day/month/year here.
I did integrate an agenda into the App (not linked to Gmail, simple agenda). I was trying to set up the occurrence system to add repetitive tasks but i’m new with formula’s (I work with appsheet for 6 months and never learned Excel formula’s before, but my technique is to download pre made apps, and observe how it works. I start to understand formulas but not really complicated ones).
I did the same here and installed the “Shift Scheduling” App than adapt it to the business and project.
But the only issue is : when i add repetitive task (shift), it does miss one day (always the tuesday) and put it on the monday. So i do have 2 tasks on monday, none on tuesday.
The formula has to be adapted but I can’t quite figure out what to do. I was thniking maybe the results are bad because of the french time format on the tables. But I need it do be displayed in French. It looks like it’s set up for a week to “start” on sunday.
To be more precise, I do have a table called “Controls” (as in the premade app). Tha table adds new entryies to an other table, which is the Agenda, called “Available”. One of the columns of “Controls” is called “Days of The Week” and is set up to “Enum”. (I will write the days in English, so we don’t get confused, and i’ll translate the days in french in formula after). In Enum i have 7 entryies with each day of the week.
Into “Behavior”, i do have a group of looped actions. The one we care about is “Loop 1 : add to available” (Agenda table).
For a record of this table : Controls
Data: add a new row to another table using values from this row :
Table to add to : Available
Set these Columns :
Key = UNIQUEID()
Location = Location
Scheduled Date = (there is the formula i’m talking about)
[Scheduled Start Date] +
IF(
SWITCH( index([Day of the Week], (floor([copies done] / [occurences]) + 1)),
“Sunday”, 1,
“Monday”, 2,
“Tuesday”, 3,
“Wednesday”, 4,
“Thursday”, 5,
“Friday”, 6,
“Saturday”, 7,
“”
) = WEEKDAY([Scheduled Start Date]),
SWITCH( index([Day of the Week], (floor([copies done] / [occurences]) + 1)),
“Sunday”, WEEKDAY([Scheduled Start Date]),
“Monday”, WEEKDAY([Scheduled Start Date])-1,
“Tuesday”, WEEKDAY([Scheduled Start Date])-2,
“Wednesday”, WEEKDAY([Scheduled Start Date])-3,
“Thursday”, WEEKDAY([Scheduled Start Date])-4,
“Friday”, WEEKDAY([Scheduled Start Date])-5,
“Saturday”, WEEKDAY([Scheduled Start Date])-6,
“”
) -
WEEKDAY([Scheduled Start Date]) +
1 +
7 * (mod([copies done], [occurences])),
IF(
SWITCH( index([Day of the Week], (floor([copies done] / [occurences]) + 1)),
“Sunday”, 1,
“Monday”, 2,
“Tuesday”, 3,
“Wednesday”, 4,
“Thursday”, 5,
“Friday”, 6,
“Saturday”, 7,
“”
) > WEEKDAY([Scheduled Start Date]),
SWITCH( index([Day of the Week], (floor([copies done] / [occurences]) + 1)),
“Sunday”, 1,
“Monday”, 2,
“Tuesday”, 3,
“Wednesday”, 4,
“Thursday”, 5,
“Friday”, 6,
“Saturday”, 7,
“”
) -
WEEKDAY([Scheduled Start Date]) +
7 * mod([copies done], [occurences]),
SWITCH( index([Day of the Week], (floor([copies done] / [occurences]) + 1)),
“Sunday”, 1,
“Monday”, 2,
“Tuesday”, 3,
“Wednesday”, 4,
“Thursday”, 5,
“Friday”, 6,
“Saturday”, 7,
“”
) -
WEEKDAY([Scheduled Start Date]) +
7 * (1+ mod([copies done], [occurences]))))
May you please help me to adapt this formula to suit or needs ?
I wish you all a great day and an awesome time working with Appsheet. I felt in love with it.
