Search code examples
exceldateexcel-formula

How calculate business days between dates

I have a simple problem, but I have already tried to solve it in several ways and did not get the expected result. I need to calculate the number of business days between two dates, but without considering the first day.

Example: initial_date: 15/08/2024 (Thursday) final_date: 20/08/2024 (Tuesday)

In Excel, if I calculate calendar days =DAYS(dt_initial;dt_final), I obtain a total of 5 days. In other words, it counts Saturday and Sunday, but that's not what I need. If I try to disregard the weekend =DIAWORKALHOTAL.INTL(dt_initial;dt_final;1), I get the result 4. That is, it includes the 15th in the account.

What I would like is to find a way to count from the next day, that is, in the example above, the result would be = 3 (days 16, 19 and 20).

If anyone knows a way to solve it, whether in Excel, PowerBI or even with Python, could you please let me know?

As I mentioned, in Excel I have already tried using =DIAWORKALHOTAL.INTL(dt_initial;dt_final;1), but it returns a count considering the starting date (the first day).

In PowerBI, I tried the example below, but got the same problem:

work_days = NETWORKDAYS(table[initial_date],table[final_date], 1)

Solution

  • Below/here refer:

    =IF(C4<C3,"",NETWORKDAYS(C3,C4)-(WEEKDAY(C3,2)<=5))

    solution.

    The reason why other calcs were not working (those in comments etc.) is primarily due to the somewhat 'cute' approach of attemting to ignore the first day by setting startdate += 1 (the day after startdate).

    This falls down, as you have noted, when start = end. My approach addresses this (and more generalized variations) appropriately/correctly - and is otherwise just as simple/parsimonious to utilize/customize.

    To demonstrate this is the correct solution here is a run-off table showcasing results (based on data-table/avail. at link above) across a range of start/end dates.

    validation

    FYI - not sure where OPs function came from given below - but did say that Excel/Python etc. would also be welcome. I stuck to Excel (per the title ☺)

    Google search result for the function provided by OP/1st commenter.

    Google search result

    (noting AI links reference this specific post!)