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.

2 Answers 2


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"))
  • 1
    Superstar! That did the trick. Thank you so much for your help Damjan! :-)
    – Glen
    Feb 6, 2019 at 9:30

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:

enter image description here

then use below Formula in the calculated column:

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

enter image description here

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

  • Thank you for your suggestion, much appreciated. The week ending column is a date so this wouldn't work for my set up. Many thanks anyway for taking the time to assist. Glen
    – Glen
    Feb 6, 2019 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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