How to return results between a certain date range

Hi I am trying to find a formula

I have a master sheet that I would like the total days of holidays of employes for each month to show on a separate sheet.

eg May total 5 days

Any Suggestions welcome thaks


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a SUMIFS.

    =SUMIFS({Data Sheet Days Taken Column}, {Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021)


    Now this only accounts for using a single date column (start OR end date). How would you count if someone's holiday overlapped into a different month? Would it be two entries (one for each month), or would it be a single entry?

Answers

  • Jen Lange
    Jen Lange ✭✭✭✭✭

    Hi @Craig Beattie. Based on the image shared above, you want to count the number of holidays (aka "Days Taken") by employee from this sheet into another sheet. Correct?

    If so, does your separate sheet include the employees name and month columns? Please advise. A COUNTIFS formula should work well for you.

    -Jen

  • Craig Beattie
    Craig Beattie ✭✭✭✭

    Hi @Jen Lange

    Thanks for the reply

    I would be adding the totals for all engineers for the given month.

    It would then fill Less Holiday Hours sheet Below


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a SUMIFS.

    =SUMIFS({Data Sheet Days Taken Column}, {Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021)


    Now this only accounts for using a single date column (start OR end date). How would you count if someone's holiday overlapped into a different month? Would it be two entries (one for each month), or would it be a single entry?

  • Craig Beattie
    Craig Beattie ✭✭✭✭

    @Paul Newcome

    Hi Paul, now just as i posted this i thought exactly the same. Unsure how i would get around this now to be honest.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    We might be able to get it squared away with some helper columns. I think we could get away with just two unless you plan on someone having a multi-month holiday from (for example) January to March. Even then we might be able to do that with two columns, but I think the easiest to manage setup would be a helper column for each month extending out to account for the maximum number of months someone's holiday could overlap into. So if Jan - March then 3 helper columns. If Jan - June would be the max then 6 helper columns.

    And all of these helper columns can be hidden after getting everything setup so that the sheet doesn't get too cluttered.


    Of course I would need to do a bit of testing to be sure, but I do have a few ideas if you've got a little time and are willing to put in a little extra work. If only overlapping into the next month, then that we can definitely do with two helper columns. Its all of the extra months that could potentially get a little tricky.


    Let me know!

  • Craig Beattie
    Craig Beattie ✭✭✭✭

    @Paul Newcome that code worked perfectly but as you say it would then become a problem if someone overlapped a month, most of the time the guys take a few days or a week or so so would only hit 2 months.

    I will have the form available for the engineers to fill out, from there it would be approved by management maybe it would be easier at this point for us to manually split the holidays overlapping to save any issues, what do you think?


    thanks again for your help

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I think that if it going to overlap into no more than two months, we should be able to get a pretty straightforward solution built using only two helper columns on the data sheet. I'll do some testing later this evening and get back to you.

  • Craig Beattie
    Craig Beattie ✭✭✭✭

    @Paul Newcome That would be great thank you again

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So we are going to insert two text/number columns called [First Month] and [Second Month] into the source sheet.


    [First Month]:

    =IF(MONTH([Start Date]@row) <> MONTH([End Date]@row), DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1) - [Start Date]@row, [End Date]@row - [Start Date]@row + 1)


    [Second Month]:

    =IF(MONTH([Start Date]@row) <> MONTH([End Date]@row), [End Date]@row - DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1) + 1)



    Then to get your total (the below formula is an example for May 2021):

    =SUMIFS([First Month]:[First Month], [Start Date]:[Start Date], AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 5)) + SUMIFS([Second Month]:[Second Month], [End Date]:[End Date], AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 5))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!