Network days between dates, less a half day marker

Need to work out the working days (Mon-Fri), excluding bank holidays, between the start and end dates in columns A + B, by month in columns E-P.

This all works fine with the current formula.

Change required:

-Need to deduct 0.5 where there is an x in either the afternoon or morning columns (Denoting half a day, instead of a full day). This can be applied to the first month where the days are split between more than 1 month. E.g. row 5 would be 0.5 days in Mar and 1 day in Apr, instead of 1 day in Mar and 1 day in Apr.

Can do it with a few ifs but thought there maybe a nice short and fancy way

Hi,

Don't know if it's fancy enough but just add

Formula:

+N((MONTH(E$1)=MONTH($A4))*(($C4="x")+($D4="X"))*-0.5)

to your formula and it will do the trick.

Originally Posted by benoj2005

Also there could be an x in both the morning and afternoon

In my addition it will denote a 0,5 day for the morning AND a half day for the afternoon so 2 times 0,5 but your original OP was denotation can be applied to the first month.

So it does but ....
if you filled in an X in both afternoon and morning and apply it to the first month if would be more feasible to register a day leave shorter without any X which will result in the same calculation.

or ....

The yearcheck wasn't in your OP but ok try this:

Formula:

=+N((YEAR(E$1)&MONTH(E$1)=YEAR($A4)&MONTH($A4))*(($C4="x")+($D4="X"))*-0.5)

Originally Posted by joris moerings

In my addition it will denote a 0,5 day for the morning AND a half day for the afternoon so 2 times 0,5 but your original OP was denotation can be applied to the first month.

So it does but ....
if you filled in an X in both afternoon and morning and apply it to the first month if would be more feasible to register a day leave shorter without any X which will result in the same calculation.

or ....

The yearcheck wasn't in your OP but ok try this:

Formula:

=+N((YEAR(E$1)&MONTH(E$1)=YEAR($A4)&MONTH($A4))*(($C4="x")+($D4="X"))*-0.5)

Thanks for the help, that looks to be working well and avoids an if with the network days formula duplicated