Help with date formula for every-other-week report

AppSheet AppSheet Q&A
,
1

So, “date math” is not my strong suit, and often I have errors that have to get sorted out when I built automations that are date-intensive. Thing is, I have an automation that needs to be deployed and error-free on the first try for various reasons, so I’m hoping someone can verify my thinking here:

I have an automation that will run on a schedule, every Monday at 1:00pm. I need it to look at a date and use it to determine if the report should run for a particular row (it’s a “for each row” automation event trigger). The criteria is the report run every other week, and the date column could be any day of a week, not just a Monday. Will the below expression in the “filter condition” of the report trigger event accomplish this?

IF(
MOD(FLOOR(TOTALHOURS(TODAY() - [date column]) / 7), 2) = 0,
TRUE,
FALSE
)

Thanks in advance for any help!

2

Hello @Patrick_Paul,

This is indeed great! I will dare to suggest removing the TOTAL HOURS function and leave it like this:

MOD(FLOOR((DAY(TODAY()) - DAY(“25/06/2026”))/7),2)

and create a Scheduled Bot following the steps below:

  1. Set the Event Type to Schedule.
  2. Set the For Each Row in Table toggle depending on whether you are running this for specific rows or the entire table.
  3. Set the Schedule frequency to Weekly.
  4. Under Days of the week, select the day you want it to run.
  5. Set the Time of day to 13:00.

This ensures your expression will be evaluated exactly at 1:00 PM UTC every 7 days.

I hope this helps!

3

Thanks… I already have the bot configured as such… In the expression: What is the significance of you using a string literal for the date rather than a date column, if any? I will need to use a date column. I’m also unsure about the “date math” going on here, since you’re proposing to pull the day number out of both dates being compared and subtracting them. Do you mind breaking this down for me? I appreciate the help, I’d just like to better understand how it works rather than just plug it in blindly. I’m not stranger to programming logic like this, but messing with dates has always been a weakness of mine.

4

Hello @Patrick_Paul,

My bad, I was doing some testing with the expression and I left a hard coded date

MOD(FLOOR((DAY(TODAY()) - DAY([date column])/7),2)

Assuming [date column] = July 2nd:

  • (DAY(TODAY()) - DAY([date column]) = July 9th - July 2nd= 7 days
  • Floor(7)/7=1
  • MOD(1,2)=0

Day() will return the day of the month (1 to 31) that a specific date falls on from Date or DateTime, the difference between TODAY() and the specified date would be 7 days for July 2nd, for example.

FLOOR() will return the nearest integer rounded down, in this example: 7/7=1

MOD() will return the remainder of dividend divided by divisor. In this case, considering July 2nd, the remainder would be 0

5

My apologies, I have been very much occupied elsewhere and I am only now getting to return to this. I appreciate the explanation! This reveals why this wasn’t quite making sense to me… In your example, you simplify it to MOD (1,2)=0, but wouldn’t that be 0.5 or 1?

Also, I believe a closing parenthesis is missing from somewhere, but unsure where it should be. Perhaps after the seven?

6

I used Gemini AI, and after a few rounds of refinement, found that the following code produced the desired result:

IF(
MOD(
TOTALHOURS(
(TODAY() - (WEEKDAY(TODAY()) - 2)) -
([Date Begun] - (WEEKDAY([Date Begun]) - 2))
) / 168,
2
) = 0,
TRUE,
FALSE
)