I wanted to know if a date falls on the last day of a 14-day period.
Since NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) returns a duration in hours, I needed to divide by 24. Then I thought I could simply divide by 14 for a 14-day period like this:
((NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) / 24 ) / 14)
But this formula always returns a rounded integer value since both values in the equation are integers. So
((NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) / 24 )/14) -
FLOOR((NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) / 24 )/14)
was always returning 0 as if every date was the last one of the period.
The solution is simply to force in decimal number in the equation like for me 14.0
((NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) / 24 )/14.0) -
FLOOR((NUMBER(INDEX(SPLIT(TEXT(TODAY() - DATE(“2025-11-01”)), “:”), 1)) / 24 )/14.0)
And now this condition allows me to display the text I want.