Hello, Is it possible to show the date of a c...

[Jenn_M]

Hello, Is it possible to show the date of a certain day in the week? For example, a task comes due every Monday. If today is Sunday, I’d like for the date to show Monday of this week. But, if today is Tuesday, I’d like for the due date to show as next week Monday. I’d also like to do this for the monthly tasks…due on the first Monday of the month.

Currently, for the weekly due, my process is to mark the task as completed, triggering a timestamp,

and then adding seven days to that timestamp.

If possible, I’d like to eliminate marking a task as completed in order to get the next due date.

Any thoughts appreciated, thanks!

[RezaRaoofi]

Since WEEKDAY(Date) returns 1 for Sunday, then I think a formula like this should return that next Monday you are looking for: TODAY()+IF(WEEKDAY(TODAY())=1, 1,

9-WEEKDAY(TODAY()))

[RezaRaoofi]

Regarding the second question about monthly tasks; this expression will return the first day of next month:

EOMONTH(TODAY(), 0) + 1

You could use similar logic to calculate the first Monday of the next month too.

[Steven_Coile]

WEEKDAY(TODAY()) gives you the weekday number (1 for Sunday, 2 or Monday, etc.) for today.

(TODAY() - WEEKDAY(TODAY())) gives you the date of Saturday of last week.

(TODAY() - WEEKDAY(TODAY()) + 5) gives you the date of Friday of this week.

(TODAY() - WEEKDAY(TODAY()) + 5 + 14) gives you the date of two weeks from Friday of this week.

[Steven_Coile]

“If today is Sunday, I’d like for the date to show Monday of this week. But, if today is Tuesday, I’d like for the due date to show as next week Monday.”

(

(TODAY() - WEEKDAY(TODAY()) + 2)

• IF((WEEKDAY(TODAY()) > 1), 7, 0) )

1. (TODAY() - WEEKDAY(TODAY()) + 2) sets the due date to Monday of this week.

2. • IF((WEEKDAY(TODAY()) > 2), 7, 0) adds seven days to the due date if today is later than Monday.

[Jenn_M]

+Steve Coile @RezaRaoofi

I couldn’t figure out a way to do this with an appsheet formula (no function to check for odd or even numbers that I can tell), but here’s what I came up with to generate a biweekly due date.

I created a [Start Date] column and a [Biweekly Due] column. [Biweekly Due] has the following spreadsheet formula:

IF(NOT(ISBLANK([Unique ID])), IF(OR( AND(ISODD(WEEKNUM([Start Date])),WEEKNUM(TODAY())), AND(ISEVEN(WEEKNUM(Companies!N2)),WEEKNUM(TODAY()))), TRUE,FALSE), “”)

Then, to generate my biweekly date in another column, I have this app formula:

IFS(OR([Schedule]=“Weekly”, AND([Schedule]=“Biweekly”, [Biweekly Due]=TRUE)),

((TODAY() - WEEKDAY(TODAY()) + [Route Week Day]) + IF((WEEKDAY(TODAY()) > [Route Week Day]), 7, 0))

Good method or no?

[Jenn_M]

Thank you both! Using that info, I’ve come up with the following with [Route Week Day] being the week day of the task:

IFS([Schedule]=“Weekly”,

((TODAY() - WEEKDAY(TODAY()) + [Route Week Day]) + IF((WEEKDAY(TODAY()) > [Route Week Day]), 7, 0)),

[Schedule]=“Monthly”,

EOMONTH(TODAY(), 0) + 1 +

IF( WEEKDAY(EOMONTH(TODAY(), 0)) + (7 - (WEEKDAY(EOMONTH(TODAY(), 0) + 1))) <= [Route Week Day],

([Route Week Day] - (WEEKDAY(EOMONTH(TODAY(), 0) + 1))),

([Route Week Day] + 1)))

I’d like to add a biweekly schedule also but not sure how to go about that.