Skip to main content

Formula to count workdays between now and a due date [SOLVED]

  • November 29, 2017
  • 6 replies
  • 106 views
K

Hi, all!

I’m looking to modify a formula I pulled from the “Freelancers Dashboard” template in Universe. The end goal is to calculate the number of working days between now and a due date in another field. The use case is calculating how many workdays I have left before I need to invoice a client.

The template’s (super complicated) formula works, but you have to manually enter how many weekends remain, and it only counts down how many days are left in the month, not down to a date field. As a use case, this isn’t helpful for clients billed on a (bi)weekly cadence.

This is how the formula is now.

I know there’s a DATETIME_DIFF and WORKDAY function, but I can’t figure out how to piece them together and include the “Next Invoice” field. Any help is appreciated!

    6 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • November 29, 2017

    I suspect you could start with the formula I posted in this reply, which calculates…

    No; wait.

    I think you could use that formula, unchanged, to get what you need. (It calculates the number of workdays between two dates; however, it doesn’t take into account holidays.) I can’t think of any reason for it not to work if you replace {Date from} with TODAY().

    K
    • Kaila_Lawrence
    • Author
    • New Participant
    • November 30, 2017

    I suspect you could start with the formula I posted in this reply, which calculates…

    No; wait.

    I think you could use that formula, unchanged, to get what you need. (It calculates the number of workdays between two dates; however, it doesn’t take into account holidays.) I can’t think of any reason for it not to work if you replace {Date from} with TODAY().


    Thanks for your reply!

    The formula works counting days left in the current month, but you have to manually enter how many weekend days and holidays remain at the end where the two 0’s are.

    Regardless, I don’t want to count down days left in the month, I want to count down workdays to the date listed in the “Next invoice” field in each record.

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • November 30, 2017

    Thanks for your reply!

    The formula works counting days left in the current month, but you have to manually enter how many weekend days and holidays remain at the end where the two 0’s are.

    Regardless, I don’t want to count down days left in the month, I want to count down workdays to the date listed in the “Next invoice” field in each record.


    I figured I must have misread your original post, so I copied the freelancer dashboard template, modified it, and posted my modification — and then re-read your most recent reply and realized I hadn’t misunderstood you. Here’s the solution you want; I’ll post the other approach (that is, the one that automates the days-left-in-month calculation) after.

    This is the formula from my reply to an earlier message on the forum.

    DATETIME_DIFF({Date to},{Date from},'days')-
    (INT(DATETIME_DIFF({Date to},{Date from},'days')/7)+
    IF(DATETIME_FORMAT({Date from},'E')>DATETIME_FORMAT({Date to},'E'),1,0))*2

    To count the number of work days between today and the next invoice date, replace {Date to} with {Next invoice} and {Date from} with TODAY(), as so:

    DATETIME_DIFF({Next invoice},TODAY(),'days')-
    (INT(DATETIME_DIFF({Next invoice},TODAY(),'days')/7)+
    IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT({Next invoice},'E'),1,0))*2

    This doesn’t support the ‘anticipated days off’ functionality of the original formula, but it should give you the number of workdays until the next invoice date.

    NOTE: My original formula assumed both beginning and ending dates always fell on a workday; I’ve not tested to see what happens when TODAY() is on a weekend.

    What follows is the first version of my reply, with the days-left-in-month calculation.

    . ____________________________________________________________

    I tracked down the template you used, and I think this does what you want. I’m not entirely sure, though, as 15 minutes ago this became the worst day of the month to test this algorithm.

    I’ve also not attempted to plug this into the existing dashboard. Also, the original formula includes a cheat to allow for planned days off to be subtracted from the ‘days left’ calculation; if you need that functionality, you’ll have to add it to the formula.

    I’ve created a demo ‘base’ that contains only the three fields (two new, one modified) you will need to add this modification to your existing dashboard. (To keep things cleaner and more easily maintained, I perform the {Days Left} calculation in three separate pieces, a field for each. You can combine them into a single formula, if you wish.)

    Briefly, I’ve created two new fields and replaced the formula for the existing {Days Left} field.

    {Month} formula field
    Formula:
    MONTH(TODAY())

    {EndOfMonth} formula field
    Formula:
    DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'MM')&'/'&
    IF(Month=1,'31',IF(Month=2,'28',IF(Month=3,'31',IF(Month=4,'30',
    IF(Month=5,'31',IF(Month=6,'30',IF(Month=7,'31',IF(Month=8,'31',
    IF(Month=9,'30',IF(Month=10,'31',IF(Month=11,'30',IF(Month=12,'31')
    )))))))))))&'/'&DATETIME_FORMAT(TODAY(),'YYYY'),'MM/DD/YYYY')

    {Days Left} formula field
    Replace formula with:
    DATETIME_DIFF(EndOfMonth,TODAY(),'days')-
    (INT(DATETIME_DIFF(EndOfMonth,TODAY(),'days')/7)+
    IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT(EndOfMonth,'E'),1,0))*2

    Again, you’ll probably want to see how this code works tomorrow. :winking_face:

      K
      • Kaila_Lawrence
      • Author
      • New Participant
      • November 30, 2017

      I figured I must have misread your original post, so I copied the freelancer dashboard template, modified it, and posted my modification — and then re-read your most recent reply and realized I hadn’t misunderstood you. Here’s the solution you want; I’ll post the other approach (that is, the one that automates the days-left-in-month calculation) after.

      This is the formula from my reply to an earlier message on the forum.

      DATETIME_DIFF({Date to},{Date from},'days')-
      (INT(DATETIME_DIFF({Date to},{Date from},'days')/7)+
      IF(DATETIME_FORMAT({Date from},'E')>DATETIME_FORMAT({Date to},'E'),1,0))*2

      To count the number of work days between today and the next invoice date, replace {Date to} with {Next invoice} and {Date from} with TODAY(), as so:

      DATETIME_DIFF({Next invoice},TODAY(),'days')-
      (INT(DATETIME_DIFF({Next invoice},TODAY(),'days')/7)+
      IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT({Next invoice},'E'),1,0))*2

      This doesn’t support the ‘anticipated days off’ functionality of the original formula, but it should give you the number of workdays until the next invoice date.

      NOTE: My original formula assumed both beginning and ending dates always fell on a workday; I’ve not tested to see what happens when TODAY() is on a weekend.

      What follows is the first version of my reply, with the days-left-in-month calculation.

      . ____________________________________________________________

      I tracked down the template you used, and I think this does what you want. I’m not entirely sure, though, as 15 minutes ago this became the worst day of the month to test this algorithm.

      I’ve also not attempted to plug this into the existing dashboard. Also, the original formula includes a cheat to allow for planned days off to be subtracted from the ‘days left’ calculation; if you need that functionality, you’ll have to add it to the formula.

      I’ve created a demo ‘base’ that contains only the three fields (two new, one modified) you will need to add this modification to your existing dashboard. (To keep things cleaner and more easily maintained, I perform the {Days Left} calculation in three separate pieces, a field for each. You can combine them into a single formula, if you wish.)

      Briefly, I’ve created two new fields and replaced the formula for the existing {Days Left} field.

      {Month} formula field
      Formula:
      MONTH(TODAY())

      {EndOfMonth} formula field
      Formula:
      DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'MM')&'/'&
      IF(Month=1,'31',IF(Month=2,'28',IF(Month=3,'31',IF(Month=4,'30',
      IF(Month=5,'31',IF(Month=6,'30',IF(Month=7,'31',IF(Month=8,'31',
      IF(Month=9,'30',IF(Month=10,'31',IF(Month=11,'30',IF(Month=12,'31')
      )))))))))))&'/'&DATETIME_FORMAT(TODAY(),'YYYY'),'MM/DD/YYYY')

      {Days Left} formula field
      Replace formula with:
      DATETIME_DIFF(EndOfMonth,TODAY(),'days')-
      (INT(DATETIME_DIFF(EndOfMonth,TODAY(),'days')/7)+
      IF(DATETIME_FORMAT(TODAY(),'E')>DATETIME_FORMAT(EndOfMonth,'E'),1,0))*2

      Again, you’ll probably want to see how this code works tomorrow. :winking_face:


      Yes! Thanks so much for the help.

      I was my misunderstanding; I didn’t realize in your first reply you were referring to the formula you linked and not the one I already had, where there was no {Date from}. That’s why I was confused!

      I’m okay with it not counting holidays. I tested it adding more records with fake dates and it works like a charm. You’re the best!

      W
      Forum|alt.badge.img+5
      • W_Vann_Hall
      • Inspiring
      • November 30, 2017

      Yes! Thanks so much for the help.

      I was my misunderstanding; I didn’t realize in your first reply you were referring to the formula you linked and not the one I already had, where there was no {Date from}. That’s why I was confused!

      I’m okay with it not counting holidays. I tested it adding more records with fake dates and it works like a charm. You’re the best!


      No problem – after I posted my last reply I realized how overly ambiguous my first response had been. I’ve got to start hitting the support forum before midnight. :winking_face:

      Glad it helped!

        P
        • Patricia_Li
        • Participating Frequently
        • January 2, 2018

        Yes! Thanks so much for the help.

        I was my misunderstanding; I didn’t realize in your first reply you were referring to the formula you linked and not the one I already had, where there was no {Date from}. That’s why I was confused!

        I’m okay with it not counting holidays. I tested it adding more records with fake dates and it works like a charm. You’re the best!


        In case you were interested in moving off of the workaround… we’ve just added a WORKDAY_DIFF formula! Documentation here.

        It takes in a start date, end date, and optional list of holidays, like so: WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10'), and returns the number of working days between the start date and end date, inclusive.

        For those of you familiar with Excel or Sheets, it’s pretty much isomorphic to the NETWORKDAYS formula in those products.

          Terms & ConditionsAccessibility statement