Solved

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

R
Rank

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

icon

Best answer by Richardheap 12 February 2023, 23:04

View original

13 replies

M
Rank
Userlevel 2
Badge

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)

 

 

R
Rank

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
Rank
Userlevel 2
Badge

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

 

R
Rank

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
Rank
Userlevel 2
Badge

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? 

 

R
Rank

 

 

 

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

M
Rank
Userlevel 2
Badge

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

 

Chris Hackett
Rank
Userlevel 7
Badge

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
Rank

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
Rank

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
Rank

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
Rank

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

.

F
Rank

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

Reply


Terms & ConditionsCookie 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