I would like a formula to tell me the number of days holiday taken this month

Amy Tomlinson

I have searched for a formula to calculate the number of days holiday, but I would like it to be more specific to tell me the number of days taken this month.

So far I have this:

=SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred") which tells me the number of days holiday, total. But I need to restrict it to either 2020 or the current month.

The next step on from here to to tell me the number of days worked in a month based on subtracting holiday and sickness from the number of working days in a month. - but I'll settle for the formula above!

Paul Newcome

Current Month

=SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = 2020))

Current Year:

=SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Paul Newcome

To calculate the working days in a month, we need to first establish the first of the month and the last of the month. Then we can plug those two dates into a NETWORKDAYS function. After that we subtract the holidays and sick days.

To get the first of the current month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

To get the last day of the month, we actually need to calculate the first day of the next month and then subtract 1. That way you don't have to constantly update anything for months containing 28, 29, 30, or 31 days.

So the first of next month starts out as

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

The problem is if we are in December. 12 + 1 = 13. Since there is no month 13, it will throw an error. So if it is throwing an error on December months, then we need to add 1 to the year and make the month 1.

=DATE(YEAR(TODAY()) + 1, 1, 1)

Now we can drop those into an IFERROR so that we run the first one first and the second one if the first throws the error. Subtracting 1 will give us the last day of the month.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Now that we have our start date as the first of the month and our end date as the last of the month, we can drop those into a NETWORKDAYS function to tell us how many working days there are in the month.

=NETWORKDAYS(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

Subtracting the holidays and sick days from that will tell you how many days should have been worked for the month.

=NETWORKDAYS(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = 2020))

Paul Newcome

Current Month

=SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = 2020))

Current Year:

=SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Paul Newcome

That second bit is definitely going to be a bit trickier, but I believe it is doable. Let's get this bit working first, and I'll be happy to try to help tackle the other.

Amy Tomlinson

Thanks Paul - that worked perfectly!

I wondered if subtracting workdays would work - something like this (but clearly this does not work :) ) =SUM(WORKDAY(TODAY(),[holidays]) - the formula above.

Thanks again

Paul Newcome

To calculate the working days in a month, we need to first establish the first of the month and the last of the month. Then we can plug those two dates into a NETWORKDAYS function. After that we subtract the holidays and sick days.

To get the first of the current month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

To get the last day of the month, we actually need to calculate the first day of the next month and then subtract 1. That way you don't have to constantly update anything for months containing 28, 29, 30, or 31 days.

So the first of next month starts out as

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

The problem is if we are in December. 12 + 1 = 13. Since there is no month 13, it will throw an error. So if it is throwing an error on December months, then we need to add 1 to the year and make the month 1.

=DATE(YEAR(TODAY()) + 1, 1, 1)

Now we can drop those into an IFERROR so that we run the first one first and the second one if the first throws the error. Subtracting 1 will give us the last day of the month.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Now that we have our start date as the first of the month and our end date as the last of the month, we can drop those into a NETWORKDAYS function to tell us how many working days there are in the month.

=NETWORKDAYS(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

Subtracting the holidays and sick days from that will tell you how many days should have been worked for the month.

=NETWORKDAYS(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1) - SUMIFS(Duration:Duration, Absence:Absence, "holiday", Assigned:Assigned, "Fred", Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = 2020))

Amy Tomlinson

Paul - that is amazing!! Thank you so much for taking the time to explain it to me as well. It was invaluable and I sincerely appreciate the learning opportunity

Paul Newcome

Happy to help! 👍️

That is actually my thought process when building more complicated formulas. I break it down into sections, get those working individually using cell references, then piece everything together.

Joachim Mund

Hi Paul,

first of all, sorry for my bad english and greetings from the north of germany.

Could you please help me to calculate the number of days per month from a list of ranges (holidays)?

The above formula seems not be the right one.

List:

Questions: how many days of holidays has Fred in march?

Thanks so much for your help and kind regards, Joachim

Paul Newcome

@Joachim Mund That's actually a little bit of a tough one. Let me do some testing and get back to you.

Paul Newcome

@Joachim Mund Am I correct in assuming that the desired result for your question of how many days were taken in March for Fred is 10? According to your screenshot, Fred has taken a total of 13 days, but not all of those days were in March. Row 2 has a net-days value of 10, but really only 7 of those were in March. The remaining 3 fell in April, so you do not want to count those. Correct?

Joachim Mund

yes thats right! 10 days for Fred in march.

The only answer I am thinking of, is to create a bunch of columns like 01-2020,02-2020, 03-2020 etc. and to calculate with networkdays the relevant days using min / max for the start and end date like:

=networkdays( max(start1;date(2020;3;1)); min(end1;date(2020;3;31))

and then sumifs to find the sum for Fred in the march column 03-2020 etc.

Glad to hear from you, if you have an easier solution.

Paul Newcome

My initial thought was to insert a helper column for each month and have a formula that would calculate them individually then sum them up (very similar to what you have above), but that doesn't scale very well if you also need to be year specific such as March of 2020 vs March of 2019.

Joachim Mund

Ok, so my idea is nearly the same as yours.

I need this for showing ressource avalabilty on a monthly base in a project sheet.

Our projects are not going over years, so I will create project plans 2020/2021 and then shift to 2021/2020 which means 24 helper columns in the project plan and in the holiday sheet.

Paul Newcome

That's pretty much it. I will still do some experimenting though to see if I can find a way to do it with less columns.

Paul Newcome

How were you intending to display this data?

Joachim Mund

in colums according to 01-2020 .... 12-2021 and rows for capacity, holiday, availability and assigned.