DATES FORMULA

chiukim · 05-29-2023 02:21 AM

I need to get the dates that is equivalent to this columns:

[frequency] = enumlist type {daily,weekly, monthly,yearly}

if frequency = daily it will show a column [every how many days] number type, which means number of days interval. so if i set daily then 3 for [every how many days] so the date to convert is dates with 3 days interval

if frequency = weekly it will show a column [days_in_a_week] enumlist type, which set a specfic days in a week. (ex. monday, saturday) in this scenario, i want to get the neariest day only from today but excluded the days that is already expired. example today is wednesday, so the date to convert is saturday.

Marc_Dillon

https://help.appsheet.com/en/articles/961221-show_if-column-constraint

chiukim

Thanks Marc. But theres no problem in show if formula. The problem here is getting the dates based on specific conditions which can be found in the additional columns to be shown if you selected a different frequency.

Example if a choose daily and set [every how many days] to 2 the expected nearest date 06/01/2023. Im just confused in the today() + [every how many days] formula. Coz the date will not actually come becoz it keeps on moving.

Marc_Dillon

Can you show an example of the inputs and the desired outputs more clearly? May be necessary to show several examples.

chiukim

Example in Daily : the expected output should be 06/01/2023 next is 06/03/2023 then next is 06/05/2023 basically interval of 2 days

Example in Weekly : The expected result should be 06/02/2023. Coz its the latest unexpired day.

Marc_Dillon

Example in Daily : the expected output should be 06/01/2023 next is 06/03/2023 then next is 06/05/2023 basically interval of 2 days

What do you mean by "next", how many outputs should there be?

How do you determine what an "expired" day is? Why wouldn't the output of the weekly example be 5/29/2023 or 5/30/2023? Is it only supposed to be one output here, or somehow multiple as above?

chiukim

just only one. what i mean next if the date if already done/lapse that date next should be followed.

expired dates meaning that date has lapsed. just only one output choosing the neariest date from today but not the lapsed date. in my example tuesday is neariest date it shouldnt be the output becoz its already lapsed date.

btw, the reason of getting these date is connected in automation. i will used it as my condition before firing a notification about there task schedule.

Marc_Dillon

For the daily, what's wrong with TODAY() + [every how many days] ?

For the weekly, I'd suggest setting up a utility table with one column the day of the week as a word, and one column the day of the week as a number. Set up your EnumList as a base-type Ref to this table. You need this to do the math easily. Then you can do a MIN(SELECT()) on the EnumList to find the lowest day number that is greater than today's day number.

chiukim

the problem with [daily] = TODAY() + [every how many days] is the actual date will not actually come. becoz today() is changing everyday right?
so if I use it as a condition [daily]=today() it will it will near be happen. I hope im making sense.

thanks for the input in weekly. i will try it.

Marc_Dillon

I assumed the output would have been calculated via expression in another real column on the table, where it wouldn't change.

chiukim

Hello @Suvrutt_Gurjar! Can i ask a help about this one from you?