Automatically dating individual sheets within an Excel workbook

Heathera1986 

I can relate, but putting all the transactions in one table instead with a date for each will be easier to deal with or manipulate with formulas.

as an example:

Here is a raw employee pay calculation dataset spanning 1/1/2020 thru 12/31/2021

if we followed your system of putting each employee in a sheet and having a sheet for each day so that would be 13 employees x time span of 1/1/2020 thru 12/31/2021 would be 4,383 sheets.

whereas in the above on 1 sheet for all 13 employees.

we could then transpose it into a usable  formatted dataset:

That we can use in a Power Pivot:

which we can selectively expand to see the details of each year aggregation for example by quarters:

or by month:

and its dynamic (automatically update) the pivot table will automatically update as data is added just by refreshing the power query pressing ctrl+alt+F5, which should refresh the power query and the pivot table.

Data Model is setup this way:

https://youtu.be/e-CFYi52gpc

Here's the article link related to the file below:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/pro-rate-salary-based-on-hire-and-fire-date-and/72f2a798-97da-46a4-bdf4-6aeccbe2e913?messageId=b88ac964-8fa7-455f-85c9-ea59b2cf390f&page=2

File: https://1drv.ms/x/s!AiUZUhiQtF3Fgt8Yh-xP2VBUiYJBiw?e=z8uU0s