0

I am building document library on SharePoint Online to store employee training certificates that can monitor their issued and expiry dates. The validity period for each certificate is different depending on what training it was for, i.e. First Aid training valid for 3years, CPR training valid for 1 year, Dangerous Goods Training Valid for 2 years, Goods Receipt & Dispatch has no renewal requirement etc.

I have created a Choice Column called "Training Certification Type" and added each training option to the dropdown bar. I have also added a date column for "Date Issued" and a person column for the "employee name".

I am now trying to add a calculated column to calculate the unique expiry dates for each certificate.

After reading a few different articles on writing formulas I tried the below formula but an error message keeps appearing. And Ideas on what I'm doing wrong or if there is a better formula to use?

IF(([Training Certification Type]="ADA"),TEXT([Date Issued]+730,DD/MM/YYY),IF(([Training Certification Type]="CPR"),TEXT([Date Issued]+365,DD/MM/YYY),IF(([Training Certification Type]="First Aid"),TEXT([Date Issued]+1095,DD/MM/YYY),IF(([Training Certification Type]="Forklift License"),TEXT([Date Issued]+1826,DD/MM/YYY),IF(([Training Certification Type]"CSE (RTO)"),TEXT([Date Issued]+1461,DD/MM/YYY), IF(([Training Certification Type]="Personal Tooling Inventory"),TEXT([Date Issued]+730,DD/MM/YYY),IF(([Training Certification Type]="Part 145 Authorisation"),TEXT([Date Issued]+730,DD/MM/YYY),IF(([Training Certification Type]="DG Acceptance"),TEXT([Date Issued]+730,DD/MM/YYY,"")))))))

Once I have this set up correctly, I want to set up a flow in power automate to alert the employee from the person column that their certificate is is expiring in 30 days, 14 days and on expiry date.

Thank you in advance!!

1 Answer 1

0

Try the below formula created based on the logic you have in your formula. (Typo on Authorization has been corrected in the formula)

[Expiry Date]

=IF(OR([Training Certification Type]="ADA",[Training Certification Type]="Personal Tooling Inventory",[Training Certification Type]="Part 145 Authorization",[Training Certification Type]="DG Acceptance"),([Date Issued]+730),IF([Training Certification Type]="CPR",([Date Issued]+365),IF([Training Certification Type]="First Aid",([Date Issued]+1095),IF([Training Certification Type]="Forklift License",([Date Issued]+1826),IF([Training Certification Type]="CSE (RTO)",([Date Issued]+1461),"")))))

[DaysAdded]

=DATEDIF([Date Issued],[Expiry Date],"d")

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.