I'm trying to use a calculated field to add an automatic warning on a request form if a delivery date less than 10 working days away is entered in a date field. | Community
Skip to main content
L
LukeCo2
January 29, 2021
Solved

I'm trying to use a calculated field to add an automatic warning on a request form if a delivery date less than 10 working days away is entered in a date field.

  • January 29, 2021
  • 1 reply
  • 692 views

I’ve been trying to crack this for ages, but I feel like I’m getting more confused and further away from a solution the longer I go. I always get a ‚ÄòCustom Expression Invalid’ error when trying to use WEEKDAYDIFF.

I expect it to be something like the below:

=IF((WEEKDAYDIFF({DE:POSDELDATE}, Today) <= 7),”Please allow 7 days for delivery”

I’m super new to the platform and have very little relevant coding experience. If anyone fancies taking pity on a newbie and pointing me in the right direction I’d be incredibly grateful. When I get my head around this thing I’ll be sure to pay the kindness forward. Thanks!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Richard_Le_

Hi Luke,

Firstly welcome to Workfront! The community is a great place to ask questions like this, there are plenty of people here happy to help!

The below code should work for you:

IF(WEEKDAYDIFF({DE:POSDELDATE},$$TODAY)<=7,'Please allow 7 days for delivery')

A couple of pointers for you:

  • Your syntax was a little off. You only needed the one opening bracket and you missed a closing bracket at the end of your statement
  • When using a wildcard variable such as 'today' or 'now', you need to add two dollar signs beforehand. So today would be $$TODAY. There's some more info on wildcards here
  • The WEEKDAYDIFF function works out the number of weekdays between two dates, but it doesn't work out working days. So if you have public holidays in the date range when your team aren't working, these wont be taken into account in the calculation. A way to avoid this is to use the WORKMINUTESDIFF function which takes into account the working hours of your staff. If you thnk this could be a problem, you could update the calculation to the following which would take into account exceptions in your work schedule like bank holidays:

IF(WORKMINUTESDIFF({DE:POSDELDATE},$$TODAY)<=4800,'Please allow 7 days for delivery')

The above calculation assumes that you have 8 hours (480 mins) in a working day, resulting in 4800 minutes for 10 working days.

Hope this helps! Please shout if you have anymore questions.

Best Regards,

Rich.

1 reply

R
Community Advisor
Richard_Le_Community AdvisorAccepted solution
Community Advisor
January 29, 2021

Hi Luke,

Firstly welcome to Workfront! The community is a great place to ask questions like this, there are plenty of people here happy to help!

The below code should work for you:

IF(WEEKDAYDIFF({DE:POSDELDATE},$$TODAY)<=7,'Please allow 7 days for delivery')

A couple of pointers for you:

  • Your syntax was a little off. You only needed the one opening bracket and you missed a closing bracket at the end of your statement
  • When using a wildcard variable such as 'today' or 'now', you need to add two dollar signs beforehand. So today would be $$TODAY. There's some more info on wildcards here
  • The WEEKDAYDIFF function works out the number of weekdays between two dates, but it doesn't work out working days. So if you have public holidays in the date range when your team aren't working, these wont be taken into account in the calculation. A way to avoid this is to use the WORKMINUTESDIFF function which takes into account the working hours of your staff. If you thnk this could be a problem, you could update the calculation to the following which would take into account exceptions in your work schedule like bank holidays:

IF(WORKMINUTESDIFF({DE:POSDELDATE},$$TODAY)<=4800,'Please allow 7 days for delivery')

The above calculation assumes that you have 8 hours (480 mins) in a working day, resulting in 4800 minutes for 10 working days.

Hope this helps! Please shout if you have anymore questions.

Best Regards,

Rich.

    L
    LukeCo2Author
    February 1, 2021

    This is brilliant. Thanks so much for your help Richard!

    Terms & ConditionsAccessibility statement

    Loading footer...