Excel data table - showing all dates between Start and End

[RobKelleher]

Hello

I am trying to build a simple dashboard for monitoring sickness and absence.

Currently, I have approx 3000 records extracted from TimeTastic.

These show user id, name, etc and a separate field for start date and end date. Records currently are based on 1 entry for each person for a period of time, so could be 1 day, 4 days , week, month etc.

Example

User Id     User                   Leave Type                  Start Date               End Date

318188	Freda Bloogs	Maternity	2018-09-03	2019-06-03
373626	Barney Rubble	Self Cert Sick Leave	2018-12-10	2019-01-03
318132	Tom Jonesey	Self Cert Sick Leave	2018-12-13	2019-01-27
357399	A N other	Holiday	2018-12-17	2019-01-04

For the reporting, I want to pivot the data and show number of days, per type, per calendar month (excluding weekends from the count). The ability to refer to a separate list to allow for local bank holidays or company shutdowns, as an additional exclusion from the count, is stage 2.

Example

Leave type	January	February	March
Maternity	3	20	21
Sick	5	7	2
Training	2	1	5

The issue I am facing is getting the week days count in the pivot, especially where the reporting period crosses over into the next month (i.e. start date 26th of January, end date 3rd of February). I suspect I need to break the data down to a more granular level, where 1 record is 1 day but I haven't figured a way to automate this.

Any ideas, please?

[#2]

@RobKelleher 

I played around with this a little bit. I think you're right about getting down to a more granular level. Here's a quick and dirty example of how that might work.

There are only three formulas here; I suspect (as is always the case with Excel) there are other, maybe more elegant ways to solve this. I use two formulas. And although this is specifically for those instances in which the days out span a month boundary, it also works (albeit awkwardly) for days within a single month.

First I calculate the number of days from the start date to the end of the month in which it started. DaysInStartMonth=EOMONTH(Start,0)-Start

Then the number of days in the second month.

DaysInEndMonth=DAYS(End,Start)-DaysInStartMonth

Then the sum: =DaysInStartMonth+DaysInEndMonth

I'm going to let you take it from here. I can imagine it would get tricky for dates spanning more than two months, but that kind of thing is fun to figure out.

When it comes to Pivot Tables, by the way, if you're not already doing this, I'm a big believer in shamelessly using "helper columns" in the basic source data table, help columns that take care of that breakout detail.

[#3 · Accepted solution]

@RobKelleher this can be done fairly easily with Power Query. You specified this was tagged with Power BI, so I'm assuming you at least know what it is and will detail how to do this below.

Create a query from your data (Data > From Table/Range). From there, just manipulate the data how you want. The key is going to be in the first step, which is to expand the table of data to include all dates from the start to the end date(s). An example of how this is done can be found here. Once you have that done, add additional columns so you can use them in pivot fields of a PivotTable. The M code would look something like the below code. This assumes your data (original table you posted at the top of your post) is named "tLeave". Column names were kept the same. It also assumes, for the 'Hours' column, that each day would be worth a value of 8 - a standard work day in the US. This is arbitrary at this point.

let
    Source = Excel.CurrentWorkbook(){[Name="tLeave"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", Int64.Type}, {"End Date", Int64.Type}}),
    #"AddCartesian" = Table.AddColumn(#"Changed Type", "Dates", each {[Start Date]..[End Date]}),
    #"ExpandCartesian" = Table.ExpandListColumn(#"AddCartesian", "Dates"),
    #"TypeDate" = Table.TransformColumnTypes(#"ExpandCartesian",{{"Dates", type date}, {"End Date", type date}, {"Start Date", type date}}),
    #"AddDayOfWeek" = Table.AddColumn(#"TypeDate", "Weekday", each Date.DayOfWeek([Dates])),
    #"FilterWeekends" = Table.SelectRows(#"AddDayOfWeek", each ([Weekday] <> 5 and [Weekday] <> 6)),
    #"AddHours" = Table.AddColumn(#"FilterWeekends", "Hours", each 8),
    #"AddMonths" = Table.AddColumn(#"AddHours", "Month", each Date.Month([Dates])),
    #"AddMonthNames" = Table.AddColumn(#"AddMonths", "MonthName", each Date.MonthName([Dates])),
    #"AddYears" = Table.AddColumn(#"AddMonthNames", "Year", each Date.Year([Dates]))
in
    #"AddYears"

Close and load to a PivotTable. In it, drag the 'Leave Type' to the Rows area, 'Dates' to the Values area, 'MonthName' to the Columns area, and 'Years' to the Columns area just above 'MonthName'. This output should look like what you're requesting. Clean up the PivotTable as desired.

I've made the adjustments in the sample file attached. 

For your second stage, it gets a bit trickier. I'd have a look at these resources...

https://datachant.com/2017/04/13/calendar-with-working-days-power-bi/

https://wessexbi.wordpress.com/2016/02/26/uk-bank-holidays-in-power-query-easter/

https://www.mrexcel.com/board/threads/power-query-lookup-to-see-if-a-holiday-falls-within-a-work-wee...

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/ (related ish)

http://www.cpearson.com/Excel/holidays.htm (for reference)

HTH

[#4]

@mathetes 

Thanks for this.

I had managed to unpivot in power query, which gave me separate records for start and end and your method worked well for that. 

With the exception of maternity dates I haven't yet had any issues with more than 2 months being bridged, so happy days

[#5]

@Zack Barresse 

Thanks for this. Very elegant and logical solution.

This defo meets the expectations I had in my head for what must be a common date problem.

Very much appreciated