Stacked bar chart for specific days within a date range

Alicia L

I have columns for Date absence starts, Date absence ends, and Type of absence.

I want to create a stacked bar chart within a dashboard that will show a bar for each day with how many employees will be gone per day with a stack for each type of absence. My trouble is figuring out a formula or summary sheet for the days in between the absence start and stop date. For example: If an employee will be gone from 4-21-25 through 4-24-25, my current summary only accounts for 4-21 and does not count the other days, since they are in a range of dates and not individually typed out.

My current chart, from its corresponding summary sheet, can only sum all the unplanned absences for the first date of absence only. It does not add the absence onto subsequent days if there is more than 1, and it does not breakdown in stacks the different types of unplanned absences for each day.

Paul Newcome

You would put the dates that you would want shown on the chart going down one date type column. Then 3 more text/number columns (one for each type) would each contain a COUNTIFS along the lines of

=COUNTIFS({Source Sheet Start Date}, @cell <= [Date Column Name]@row, {Source Sheet End Date}, @cell >= [Date Column Name]@row, {Source Sheet Type Column}, @cell = "Specific Absence Type")

Alicia L

Thank you! My chart now works perfectly.

Paul Newcome

You would need to use another sheet with formulas containing cross sheet references to get this.

Alicia L

What would the formula look like to pull dates out of a range of dates and not have to type out each date?

Paul Newcome

You would put the dates that you would want shown on the chart going down one date type column. Then 3 more text/number columns (one for each type) would each contain a COUNTIFS along the lines of

=COUNTIFS({Source Sheet Start Date}, @cell <= [Date Column Name]@row, {Source Sheet End Date}, @cell >= [Date Column Name]@row, {Source Sheet Type Column}, @cell = "Specific Absence Type")

Alicia L

Thank you! My chart now works perfectly.

Paul Newcome

Happy to help. 👍️