Forum Discussion
Calculated field
I don't know if you can do this with a calculated column, but I would do it with a Power Apps customised form and do the logic there instead of using the default SharePoint form.
This is the SharePoint list and you can see that it achieves what you want. The ItemDate, ExpiryDate and CalcExpiryDate are all Date/Time columns. The TermsDays column is a number column:
This is the customised Power Apps form (from the Power Apps menu above your list). I have renamed the datacards, labels and data fields so that they are understandable.
In the Default Date of the dataCalcExpiryDate you need to enter the following expression:
If(IsBlank(dataExpiryDate.SelectedDate), DateAdd(dataItemDate.SelectedDate,Value(dataTermDays.Text)), dataExpiryDate.SelectedDate)
which means that if the dataExpiryDate is blank then add the value in the dataTermDays field to the dataItemDate selected date, otherwise (i.e it's not blank) then use the selected date in the dataExpiryDate field.
One gotcha: due to the way Power Apps handles null values in a datepicker (or rather it doesn't due to a programming oversight apparently) you need to turn on the experimental feature shown below. If you don't do this then if you delete the date in the dateExpiryDate field and save it won't delete it and will keep the previous value saved in the list.
And you can see it in action on the attached video.
Rob
Los Gallardos
Microsoft Power Automate Community Super User