Count days between dates, with complex rules

[Lovepreet323]

How to calculate days correctly between two days excluding 2nd and 4th Saturday and all sunday and excluding holidays.
Start date is counted as zero day
End date should be yesterday date

Example:-
start date is (27-Nov-2025)+1
End date is today()-1
In between Sunday on 30-Nov-2025
This givs me result as 4, but actual days is 5

Edit: Changed subject, was How to calculate days correctly between two days excluding 2nd and 4th Saturday and all sunday and excluding holidays  Make your post understandable by others  -- MrProgrammer, forum moderator

[Lupp]

Why did you post this question in the "Macros" branch?
How should somebody trying to help you know YOUR holidays?
Depending on traditions there may be "moving holidays".
The federal rebublic I live in has 16 member states, and each one can define holidays independently. In the member state I live in some holidays depend in addition on regional specifics.

There is a standard function NETWORKDAYS() to help a bit with such stuff, and it was enhanced in LibreOffice. Nevertheless you can't avoid some handiwork if you want to apply badly considered rules. I feel sure that a macro will not be the appropriate choice.
You may list all the dates in the possible range in a column, and decide in a second column for each date if it is a workday in your sense or not. Concerning the weekends and the funny distinction for saturdays this can be done with the help of standard formulas. Concerning the holidays you will need a next column for the "exceptions".

[Lupp]

I found the time to create an example for a solution similar to what I described above.

aoo555274_verySpecialWorkdayCounting.ods

(54.67 KiB) Downloaded 217 times

@MrProgrammer: The thread should be moved to the Calc forum.

[MrProgrammer]

@MrProgrammer: The thread should be moved to the Calc forum.

This means that the OP is unlikely to receive assistance with a macro, which was perhaps their goal. Another moderator had already taken that action before I saw your note. You can ask for help from the moderation team without naming an individual by reporting the post (❗️icon) and putting your reason in the report. Lovepreet323 can request that the topic be moved back to the Macros and UNO API forum if they really want a macro, though perhaps Lupp's method may provide the solution they seek.

[karolus]

Hallo
extend your List of holidays with:

Code: Select all

=DATE(2025;ROW(A1);14)-MOD(WEEKDAY(CURRENT());7)

pull down, and in the next Column add 14 to each date
from previous formula.

and for the calculation use

Code: Select all

=NETWORKDAYS( start_date;end_date;the_range_with_holydays;{1;0;0;0;0;0;0})

[Lupp]

The questioner seems to use AOO 4.0.
In AOO the function NETWORKDAYS(() still accepts only 3 arguments.

[karolus]

The questioner seems to use AOO 4.0.
In AOO the function NETWORKDAYS(() still accepts only 3 arguments.

maybe … the questioner has now the chance to upgrade!