Automatically dating individual sheets within an Excel workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-2793077%22%20slang%3D%22en-US%22%3EAutomatically%20dating%20individual%20sheets%20within%20an%20Excel%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793077%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20daily%20spreadsheet%20set%20up%20for%20each%20day%20of%20the%20month.%20There%20are%2031%20sheets%20in%20this%20workbook.%20A%20new%20workbook%20is%20downloaded%20from%20a%20master%20copy%20every%20month.%20I%20am%20trying%20to%20set%20it%20up%20so%20that%20you%20can%20type%20the%20date%20in%20the%20first%20sheet%20of%20the%20undated%20workbook%20and%20it%20automatically%20fills%20in%20the%20dates%20for%20the%20rest%20of%20the%20month.%20I%20have%20several%20individual%20businesses%20using%20this%20form%20and%20am%20trying%20to%20make%20it%20easy%20for%20them%20to%20use.%20Dating%20the%20sheets%20correctly%20can%20be%20a%20persistent%20issue.%20I%20am%20currently%20using%20the%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D('MONTH%20(1)'!%24A%241)%2B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E**each%20sheet%20is%20dated%20at%20the%20tab%20as%20well**%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%2C%20if%20there%20aren't%2031%20days%20in%20the%20month%2C%20the%20final%20sheet%20that%20sums%20up%20all%20of%20the%20activity%20for%20the%20rest%20of%20the%20month%20is%20dated%20for%20the%20following%20month.%20For%20example%3A%20My%20September%20spreadsheet%20is%20dated%20October%201st%20on%20the%20final%20sheet.%20It's%20a%20problem%20because%20I%20have%20accountants%20using%20this%20and%20the%20date%20being%20incorrect%20could%20be%20an%20issue.%20I%20know%20that%20I%20can%20remove%20the%20formula%20from%20the%20last%20day%2C%20but%20am%20trying%20to%20avoid%20that.%20Even%20an%20error%20message%20would%20be%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2793077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2793787%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20dating%20individual%20sheets%20within%20an%20Excel%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1169291%22%20target%3D%22_blank%22%3E%40Heathera1986%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewow%20old%20school.%20why%20do%20you%20need%20a%20sheet%20for%20each%20day%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795353%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20dating%20individual%20sheets%20within%20an%20Excel%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795353%22%20slang%3D%22en-US%22%3EWe%20dont%20have%20a%20system%20that%20runs%20all%20of%20our%20reports%20the%20way%20we%20want%20them%2C%20so%20we%20break%20down%20what%20money%20is%20collected%20each%20day%20by%20how%20and%20what.%20It%20then%20gets%20totalled%20up%20for%20the%20month%20at%20the%20end.%3C%2FLINGO-BODY%3E
New Contributor

I have a daily spreadsheet set up for each day of the month. There are 31 sheets in this workbook. A new workbook is downloaded from a master copy every month. I am trying to set it up so that you can type the date in the first sheet of the undated workbook and it automatically fills in the dates for the rest of the month. I have several individual businesses using this form and am trying to make it easy for them to use. Dating the sheets correctly can be a persistent issue. I am currently using the formula: 

 

=('MONTH (1)'!$A$1)+1

 

**each sheet is dated at the tab as well**

 

The issue is, if there aren't 31 days in the month, the final sheet that sums up all of the activity for the rest of the month is dated for the following month. For example: My September spreadsheet is dated October 1st on the final sheet. It's a problem because I have accountants using this and the date being incorrect could be an issue. I know that I can remove the formula from the last day, but am trying to avoid that. Even an error message would be better.

3 Replies

@Heathera1986 

 

wow old school. why do you need a sheet for each day?

We dont have a system that runs all of our reports the way we want them, so we break down what money is collected each day by how and what. It then gets totalled up for the month at the end.

@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

Yea_So_0-1632937676393.png

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:

Yea_So_1-1632937979512.png

That we can use in a Power Pivot:

Yea_So_2-1632938106146.png

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

Yea_So_3-1632938253141.png

or by month:

Yea_So_4-1632938298666.png

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:

Yea_So_5-1632938503145.png

 

https://youtu.be/e-CFYi52gpc

 

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

Pro rate salary based on Hire and Fire date and adding 3 percent base - Microsoft Community

 

File: My salary_Increment_Calculation Final.xlsx