Enter the data automatically which makes the difference between the months

[Cannone_Cannone]

Hi everyone, I'm looking for help solving the following.
In the "Training" table, in the "Training date" column, I manually entered the date of the course. Also in the "Training" table I have another column "Training categories" which is linked as a reference to the "Courses" table.
basically when I enter the date of the course it automatically fills in the duration of the course in months, for example course 1 will last "24 months".
in practice I would like to be able, if possible, by entering the date of the course to automatically fill in the "Expiring date" column with a date. If the value of the date is within the number of months, enter valid, otherwise 1 day before the deadline write expired.
I don't know if it's possible in Excel I can do it with formulas, but I don't want to burden the application.
Thank you all

[lynchk21 · Accepted solution]

These were for the excel spreadsheet since you said "I don't know if it's possible in Excel I can do it with formulas, but I don't want to burden the application." I thought you wanted to avoid more calculation within AppSheet. Do you want to do this within AppSheet?

[lynchk21 · Accepted solution]

In AppSheet, try using the following as an App formula for [Expiring Date]:

• DATE(
  YEAR([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))),
  MONTH([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))),
  DAY([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))) - 1
  )

[Cannone_Cannone · Accepted solution]

Hi, thanks again for your support, the second formula seems to work, I realized that the first formula has an error, in the example Expired date should be 5 years (60 months) so it should return the date 0101/2025

🙄

[lynchk21]

To get the value in column M, you can use:

• =edate(K2,L2)-1

Then for column N you can use:

• If(TODAY()<M2, "Valid","Expired")

[Cannone_Cannone]

Hi, thank you for your interest, I don't quite understand whether I have to insert the formulas you wrote to me in the appsheet or in the excel sheet

[lynchk21 · Accepted solution]

These were for the excel spreadsheet since you said "I don't know if it's possible in Excel I can do it with formulas, but I don't want to burden the application." I thought you wanted to avoid more calculation within AppSheet. Do you want to do this within AppSheet?

[Cannone_Cannone]

hi sorry I expressed myself as male!! yes basically I would like to do it in the application, can you give me a hand? do you need more information? Thank you

[lynchk21 · Accepted solution]

In AppSheet, try using the following as an App formula for [Expiring Date]:

• DATE(
  YEAR([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))),
  MONTH([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))),
  DAY([Training Date] + (FLOOR(([Training Date] + "0001-01-01" + [Validity Months]) / 365.25) - FLOOR([Training Date] / 365.25))) - 1
  )

[Cannone_Cannone]

Hi, thank you for your help, but I'm getting this error

[lynchk21]

Doing some looking, maybe this is the proper approach:

• EOMONTH([Training Date], ([Validity Months] - 1) + DAY([Training Date]))

I am not too familiar with EOMONTH, but I hope this works

[Cannone_Cannone]

HI
Thank you, your formula works very well!!! Do you happen to have a suggestion on how to insert the value (Valid) (expired) (expiring) in the "Validity" column?
(expiring) should change the value in the "Validity" Column 3 months before, I don't know if it's possible.Maybe I should do this in the formatting rule?

[lynchk21]

Something like this should work:

IF(TODAY()>[Expiring Date],"Expired",IF(TODAY()>=[Expiring Date]-90,"Expiring",IF(TODAY()<[Expiring Date]-90,"Valid","")))

[Cannone_Cannone · Accepted solution]

Hi, thanks again for your support, the second formula seems to work, I realized that the first formula has an error, in the example Expired date should be 5 years (60 months) so it should return the date 0101/2025

🙄

[lynchk21 · Accepted solution]

Oops some misplaced ( )... should be:

• EOMONTH([Training Date], ([Validity Date]-1)) + DAY([Training Date])

Not

• EOMONTH([Training Date], ([Validity Months] - 1) + DAY([Training Date]))

Sorry! Hope that works now

[Cannone_Cannone]

Hi thanks now it works perfectly!!
I just have other problems😫