To generate dates every Thursday in Appsheet

[mingrong9757]

I have cut off date value of 'Every Thursday'. How can I generate finance cut off date in dd/mm/yyyy for the month if I have invoice_date as the starting point and invoice_due_date as the ending point. 

Appreciate the help please. Thank you.

[lynchk21]

Do you need a list of every Thursday that falls between two dates (invoice_date and invoice_due_date) in a single list column? 

[mingrong9757]

yes preferably this is ideal

[lynchk21]

I would recommend creating another table 'Calendar' that has a date type column. You would then just need to populate it with dates for every Thursday. You would then be able to use:

• SELECT(Calendar[Date],AND([Date]>=[_thisRow].[invoice_date],[Date]<=[_thisRow].[invoice_due_date]))

Otherwise, you can work with a bot to set up a looping action, but this seems like a more difficult route. 

[AleksiAlkio]

If you are looking for the next Thursday and its date, you could use something like..
EOWEEK([Invoide date])+IF(WEEKDAY([Invoice date]>4,5,-2)

[AleksiAlkio]

What is your goal exactly? Do you want to know the latest Thursday from the same month based on the invoice_date or invoice_due_date? Or do you want to show all Thursdays between the invoice_date and invoice_due_date and select one of them? What happens if these two dates are in different month? As you can see, you need to elaborate your case a little 🙂

[mingrong9757]

Ideally both way works because my goal exactly is to find the last thursday closest to the due date. So I could create an email reminder bot to send user. To generate every thursday's date is a bonus.

[lynchk21]

For the last Thursday before/on the [invoice_due_date]:

IF(EOWEEK([invoice_due_date])-2<=[invoice_due_date], EOWEEK([invoice_due_date])-2,EOWEEK([invoice_due_date])-9)

[mingrong9757]

The invoice_due_date might be in a different month as well.

[mingrong9757]

I need some help in identifying what is wrong with the below errors. The dates are able to set as accordingly to payment_term. However at times it is a bit buggy which I guess is due to this error.

I have 3 actions that I used to set dates that performs a set, loop and repeat as shown below

The repeat action is used inside a bot as shown below