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

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!

Best Answers

«1

Answers

  • Paul Newcome
    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.

    thinkspi.com

  • 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 - 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
    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.

    thinkspi.com

  • Joachim Mund
    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
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • Paul Newcome
    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?

    thinkspi.com

  • Joachim Mund
    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
    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.

    thinkspi.com

  • Joachim Mund
    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
    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.

    thinkspi.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How were you intending to display this data?

    thinkspi.com

  • Joachim Mund
    Joachim Mund ✭✭✭✭✭

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