listexcel-formulasharepointcalculated-field

Sharepoint Calculated Field to Compare an Expiry Date to Today


I am working on a system to record the dates which training is undertaken, calculating how long it is valid for, and recording a date for when it is required to be updated.

I have a date field for when the Training Expires, and I want a calculated field that compares today's date to the date of expiry. If the date is 6 months away or less, then it will be in a Caution status, and if the value has gone past expiry, it is expired.

My plan is to include a Calculated Field in the sharepoint list that this is held in, receiving data from a PowerApp.

The Expiry Date field is calculated and formatted as a Date and Time Field but only uses Dates. My current calculation is this:

=IF(DATEDIF([TrainingLog_ExpiryDate],NOW(),"m")=<0,"Expired",IF(DATEDIF([TrainingLog_ExpiryDate],NOW(),"m")=<6,"Caution",IF(DATEDIF([TrainingLog_ExpiryDate],NOW(),"m")>6,"Safe","Error")

This is coming up with a Syntax error, and I do not really know why as I'm getting no errors other than syntax. Can anyone help, or even offer a better solution for this?


Solution

  • Please use following formula.

    =IF(TrainingLog_ExpiryDate<=TODAY(),"Expired",IF(TrainingLog_ExpiryDate<=TODAY()+180,"Caution",IF(TrainingLog_ExpiryDate>TODAY()+180,"Safe","Error")))

    Result:

    enter image description here