Skip to main content
Solved

Does anyone have a function to calculate the number of work days between two dates?

R
Freshman II

I am trying to calculate the number of work days (Mon - Fri) between 2 dates in order to work average daily sales etc.

 

I came up with this and it seems to work but feels clunky.

 

=(DateDiff('d', [PeriodStart], [Now])) + 1 - DateDiff('w', DateAdd([PeriodStart], 'd' , 0 -(DayOfWeek([PeriodStart]) -1)) , DateAdd([Now], 'd', 2 - DayOfWeek( [PeriodStart]) )) * 2 - Switch((DateDiff('d', [PeriodStart], [Now]) + 1) % 7 > 0 And DayOfWeek([PeriodStart]) = 1, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) = 8, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) > 8, 2,True, 0)

 

Appreciate if someone has something more concise.

 

thx

Best answer by Richardheap

Hi,

These formulas work for me (using MYOB Adv. version of Acumatica in Australia).

There is an Acumatica version provided earlier in the conversation that seems to be more popular and is similar to ones seen on SQL chats.

Fairly certain you should be able to find examples online for whichever query tool you are using.

View original
    Did this topic help you find an answer to your question?

    13 replies

    M
    Acumatica Moderator
    Forum|alt.badge.img
    • mrysev
    • Acumatica Support Team
    • 13 replies
    • March 29, 2022

    Hi Richard, here is some formula that looks less clunky.
    =(DateDiff('d', [PeriodStart], [Now])) + 1 - 2*(DateDiff('w', [PeriodStart], [Now]))-iif(DayOfWeek( [PeriodStart])=1, 1, 0)-iif(DayOfWeek( [Now])=7, 1, 0)

     

     

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    R
    Freshman II
    • Richardheap
    • Author
    • Freshman II
    • 6 replies
    • March 29, 2022

    Thanks for that response - great to be part of this. 

    I did try something similar but was getting the wrong result due to the way day numbering in Acumatica was applied.

    February will usually have 20 work days for a full month (e.g. always 4 full weeks) but that calc gives me 22.

     

     

    M
    Acumatica Moderator
    Forum|alt.badge.img
    • mrysev
    • Acumatica Support Team
    • 13 replies
    • March 30, 2022

    Are you sure you copy-paste it correctly? It works pretty good for me:

     

    R
    Freshman II
    • Richardheap
    • Author
    • Freshman II
    • 6 replies
    • March 30, 2022

    I think it copied fine. 

     

    The issue may be the database?

    I saw a similar formula used on an SQL forum and it did not work; but all our sites are MYSQL so l am wondering if that is a difference.

     

    When l run the DayofWeek function on a Sunday = 1 and Saturday = 7.

    I assume you get the same result?

    M
    Acumatica Moderator
    Forum|alt.badge.img
    • mrysev
    • Acumatica Support Team
    • 13 replies
    • March 30, 2022

    In this case you can try to narrow it down. 

    Specifically to split the formula by parts. Here I took 4 parts of formula above:
     

    and here is the result:

     

     

    so the total would be:
    27+1 - 8 - 0 - 0 = 20
    Do you have the same results for small parts? 

     

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    R
    Freshman II
    • Richardheap
    • Author
    • Freshman II
    • 6 replies
    • March 30, 2022

     

     

     

    =27 + 1 - 6 - 0 - 0 = 22

    M
    Acumatica Moderator
    Forum|alt.badge.img
    • mrysev
    • Acumatica Support Team
    • 13 replies
    • March 30, 2022

    So that means that DateDiff(week) does not work properly. 
    Please report a case and we will take a look.

     

    R
    1 person likes this
    • R
      Richardheap
    Chris Hackett
    Community Manager
    Forum|alt.badge.img
    • Chris Hackett
    • Acumatica Community Manager
    • 2732 replies
    • April 26, 2022

    Hi @Richardheap were you able to resolve your issue? Thank you!

    Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
    F
    Freshman I
    • fipoca
    • Freshman I
    • 3 replies
    • February 12, 2023
    Richardheap wrote:

    I am trying to calculate the number of work days (Mon - Fri) between 2 dates in order to work average daily sales etc.

     

    I came up with this and it seems to work but feels clunky.

     

    =(DateDiff('d', [PeriodStart], [Now])) + 1 - DateDiff('w', DateAdd([PeriodStart], 'd' , 0 -(DayOfWeek([PeriodStart]) -1)) , DateAdd([Now], 'd', 2 - DayOfWeek( [PeriodStart]) )) * 2 - Switch((DateDiff('d', [PeriodStart] contagemdedias [Now]) + 1) % 7 > 0 And DayOfWeek([PeriodStart]) = 1, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) = 8, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) > 8, 2,True, 0)

     

    Appreciate if someone has something more concise.

     

    thx

    Hi. I want to find out how many working days (excludes weekends and holidays) between two dates in power query editing. I know there is a formula to use in excel, however I am hoping to have the calculation done behind the scene. Can anyone shed some light on it? the file is attached. I am still fairly new to power query, as I find it good to keep the file much smaller.

    F
    Freshman I
    • fipoca
    • Freshman I
    • 3 replies
    • February 12, 2023
    Richardheap wrote:

    I am trying to calculate the number of work days (Mon - Fri) between 2 dates in order to work average daily sales etc.

     

    I came up with this and it seems to work but feels clunky.

     

    =(DateDiff('d', [PeriodStart], [Now])) + 1 - DateDiff('w', DateAdd([PeriodStart], 'd' , 0 -(DayOfWeek([PeriodStart]) -1)) , DateAdd([Now], 'd', 2 - DayOfWeek( [PeriodStart]) )) * 2 - Switch((DateDiff('d', [PeriodStart], [Now]) + 1) % 7 > 0 And DayOfWeek([PeriodStart]) = 1, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) = 8, 1,(DateDiff('d', [PeriodStart], [Now]) + 1) % 7 + DayOfWeek( [PeriodStart]) > 8, 2,True, 0)

     

    Appreciate if someone has something more concise.

     

    thx

    Hello. Did you know if it’s possible to calculate the number of working days between two dates?

    R
    Freshman II
    • Richardheap
    • Author
    • Freshman II
    • 6 replies
    • Answer
    • February 12, 2023

    Hi,

    These formulas work for me (using MYOB Adv. version of Acumatica in Australia).

    There is an Acumatica version provided earlier in the conversation that seems to be more popular and is similar to ones seen on SQL chats.

    Fairly certain you should be able to find examples online for whichever query tool you are using.

    R
    Freshman II
    • Richardheap
    • Author
    • Freshman II
    • 6 replies
    • February 12, 2023

    I think your issue is the holidays.  The formulas only refer to excluding weekends.

    As holidays are state based (over here anyway) you would need a specific calendar based table to refer to /count the days

    .

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    F
    Freshman I
    • fipoca
    • Freshman I
    • 3 replies
    • February 14, 2023
    Richardheap wrote:

    Hi,

    These formulas work for me (using MYOB Adv. version of Acumatica in Australia).

    There is an Acumatica version provided earlier in the conversation that seems to be more popular and is similar to ones seen on SQL chats.

    Fairly certain you should be able to find examples online for whichever query tool you are using.

    thank you so much for your suggestion

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved