Hi.
I want to input the employment of an employee by start date and end date. Store the data in a (access) database. Link a Excel pivot table and use a Time Line (slicer) to view the employee employment.
The key problem(?) is how to convert start & end date to a data series that can be used in pivot table.
Today I calculate all months in the time period and set :
Stored data:
Start = January 1st 2018
End = End of July 2018.
Employment = 100%
And then calculate : Jan-2018 = 100%, Feb-2018 = 100%, Mars = 100% etc…
(100% = fulltime employee)
This works as long as I only use months as “time” in the Pivot table. (COLUMNS = Month)
But I cannot use weeks or days. Even if the input data should make it possible.
( I.e week 03 .. week 12 = 100% or 15 of february to 25 of May = 100%)
Must I calculate the start&end date to a months table and a week table and a day table ?
Or is there a smart way to only use start&end dates?
I want to input the employment of an employee by start date and end date. Store the data in a (access) database. Link a Excel pivot table and use a Time Line (slicer) to view the employee employment.
The key problem(?) is how to convert start & end date to a data series that can be used in pivot table.
Today I calculate all months in the time period and set :
Stored data:
Start = January 1st 2018
End = End of July 2018.
Employment = 100%
And then calculate : Jan-2018 = 100%, Feb-2018 = 100%, Mars = 100% etc…
(100% = fulltime employee)
This works as long as I only use months as “time” in the Pivot table. (COLUMNS = Month)
But I cannot use weeks or days. Even if the input data should make it possible.
( I.e week 03 .. week 12 = 100% or 15 of february to 25 of May = 100%)
Must I calculate the start&end date to a months table and a week table and a day table ?
Or is there a smart way to only use start&end dates?
