SharePoint Calculated Column depending on future date

Question

I'm trying to create a calculated column which will return a specific name based on the date in another column.

If the 'week ending' column date is within 28 days, I need the calculated column to say JOHN.

If the 'week ending' column date is more than 28 days away, I need the calculated column to say KARL.

I've been going around in circles and thought this would work, but it does not.....

=IF([Week Ending]<28,"KARL",IF([Week Ending]>28,"JOHN"))

….all of the entries say 'KARL' regardless of the date.

Any help would be greatly appreciated.

Answer 1

You should be able to do it with this formula:

=IF(ISERROR(DATEDIF(TODAY(),[Week Ending],"d")), "KARL", IF(DATEDIF(TODAY(),[Week Ending],"d") > 28, "JOHN", "KARL"))

Answer 2

Does the week ending column show a date or the difference between two dates(week ending date and Today)?

Is the week ending column a date column or a text column?

If the week ending column is a text column(display a number), you should change the type of this column to a number column:

then use below Formula in the calculated column:

=IF([Week Ending]<28, "JOHN", "KARL")

Note: If the week ending column has no value, the calculated column will display "JOHN", because the value is within 28.