Expression to get the datetime of the same day of the week as a given datetime next month

[Patrick_Paul]

I need to figure out an expression that will take a given "datetime" field and use it to compute the datetime of the same day/week in the following month for a recurring scheduled event.  For example:

If the field contains 2/8/2024 15:00, I need it compute 3/7/2024 15:00 because that is the second Thursday in the following month (basically the same functionality as Google calendar's repeat every week function).  I've been wracking my brain and can't figure out a good way to do it in Appsheet's syntax (could do it all day in many other languages).

[Vincent_P]

Hello, 

if the gap between the two dates is one month, so just add 4 weeks : lookup("your previous date") + 28

and maybe complete it with if date+28 is in the same month so add a fifth week

[Patrick_Paul]

Thanks Vincent, but that won't reliably get me to the exact same day of the week consistently each month (different number of days in each month).  I'd just add +30 if I wasn't worried about the day of the week.

[JDuval_100]

Could you use the Day() and Month() functions?

I haven't tested this if it would give you the wright functionality , but something like

if(AND(NOT(MONTH([NOW()]) = MONTH([PreviouseDateTime])),Day([NOW()]) = (Day([PreviousDateTime]))), (Show true value) , (show false value)) 

I think that is the correct syntax. but the idea is to extract the month, and compare it to make sure the previous DATETIME and current DATETIME month value is different, AND extract the day and compare it to see if it is the same. IF both of those are true you can display the true value, in this case it would be your repeated task. and if its not true it wouldn't show. 

[Vincent_P]

That was I said add 28 days and not 30. And if you are in the same month you re add 7 days.

[Patrick_Paul]

I appreciate this attempt also, but I'm looking to extrapolate a future datetime based off an existing datetime, not evaluate versus the current date.  Again, such as:  Today is 2/9/2024, the second Friday of the month.  I'd need the expression to give me 3/8/2024, the second Friday of next month, then when that rolled around, it would give me 4/12/2024 because that's the second Friday in April and so on.  I'd have a 'bot check this monthly on a schedule and update the date.

[JDuval_100]

I see. You might be able to use 

• WEEKDAY() for the day number from a Date. Sunday is 1, Saturday is 7.

you can look here for more information on date calculations.

https://support.google.com/appsheet/answer/10107326 

[Patrick_Paul]

Yes, I've reviewed that, and believe it may be useful, but I have not found an effective implementation.

[Suvrutt_Gurjar]

---

@Patrick_Paul wrote:

Today is 2/9/2024, the second Friday of the month.  I'd need the expression to give me 3/8/2024, the second Friday of next month, then when that rolled around, it would give me 4/12/2024 because that's the second Friday in April and so on.  I'd have a 'bot check this monthly on a schedule and update the date.

---

I think in general achieving it through AppSheet expressions will be a bit complex .

Also you may want to revisit your requirement as I believe it will have some edge  cases that you may want to mention how to address.

For example in March 2024 there are 5 each Fridays, Saturdays and Sundays. However in April 2024 there are just 4 Fridays, Saturdays and Sundays. So where should 5th Friday/Saturdays and Sundays of March 2024 should roll over? Such edge cases will remain in each month. You may want to mention that.

On the other hand there are just 4 Mondays and Tuesdays in March 2024 but 5 Mondays/ Tuesdays in April 2024 , so those April 2024 5th Mondays/Tuesdays will remain unutilized as no rollover will happen to those April 2024 days?

However as mentioned in spite of these edge cases clarifications, achieving it in AppSheet could be a bit longish expression but maybe some community colleague will come up with it.

March 2024

April 2024

[Patrick_Paul]

Yep, I haven't even gotten that far yet.  I'd be happy if it could just deal with non-edge cases!

[Suvrutt_Gurjar]

Okay, got it. Please try an expression of 

IF(

 CEILING(DAY([Current_Month_DateTime]+28)/7.00) =               CEILING(DAY([Current_Month_DateTime])/7.00),

                              [Current_Month_DateTime]+28,
                               [Current_Month_DateTime]+35
      )

This will give the date time of the same weekday next month in the same week as current month in a .Datetime type column.

[Current_Month_DateTime] is the date time column of the current month.

Tested for non edge cases. We can add logic if feasible for edge cases, after you decide how to manage those edge cases ( basically dates in the 5th week of the month)

Test results below

The testing is done in an app with dd/mm/yyyy format. So the test results are also in dd/mm/yyyy format. However the expression will work in mm/dd/yyyy format as well.