Discussions

Expand all | Collapse all

I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

  • 1.  I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Posted 04-27-2017 12:34
    I need a formula to Subtract Today from a date and return the difference in weekdays only if another date field is blank. So basically I want to know how many days late an order is if it hasn't shipped. I have a order due date field and a ship date field. 

    So I want to count the number of working days past due if the ship date field is blank.

    Thanks for your help. 


  • 2.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Silver
    Contributor
    Posted 04-27-2017 12:52
    Formula Numeric field would go like --

    if(
    isnull([ship date]),
    weekdaysub(Today(),ToDate([order date])),null
    )


  • 3.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Posted 04-27-2017 13:18
    Thank you for the help. I am getting the following error.
    The types of the arguments or the number of arguments supplied do not meet the requirements of the function ToDate.

    The function ToDate can be used with the following arguments:
    • ToDate (Text x).
    • ToDate (Text x, Text f).
    • ToDate (Date/Time x).


  • 4.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Silver
    Contributor
    Posted 04-27-2017 13:21
    The [Order Date] field may already be a Date type, so just remove the function ToDate() that is wrapped around the [Order Date] field.


  • 5.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Posted 04-27-2017 13:28
    So this is what I have now and it is not returning a value.

    if(isnull([Ship Date]),
    weekdaysub(Today(),([Booked Date])),null
    )


  • 6.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Posted 04-27-2017 14:06
    I was able to get it to work by changing the formula type. Thank you!


  • 7.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Silver
    Contributor
    Posted 04-27-2017 14:11
    I believe it has to be formula numeric. Thanks for the follow up.


  • 8.  RE: I need a formula to Subtract Today from a date and return the difference in weekdays only if another field is blank

    Bronze
    Contributor
    Posted 04-27-2017 14:11
    Check the setting on your Ship Date field for "Treat blank values as '0' in calculations". Make sure that is unchecked.