How to return results between a certain date range

Craig Beattie

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

Paul Newcome

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?

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.

https://help.smartsheet.com/function/countifs

-Jen

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

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

@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

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

@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

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

@Paul Newcome That would be great thank you again

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))