I issue invoices and progress reports on the 15th and last day of each month. My reports compile work that was done in the days leading up to that deadline. I also issue Payroll reports weekly - depending on the client - end of the week may be Sunday, Friday or Thursday. So any given record will be reported for Payroll on a weekly basis (if it is a time record) or invoicing / progress reports (time and receipts).
At present I have created a Date table that matches each date to the corresponding Payroll cut off or Invoice cut off and then does a lookup to bring in the Payroll Report date and the Invoice / Progress date. My Date table needs to be updated periodically (right now it is good until Dec 31 2023).
I just wonder if there is a formula I can use to get these dates to load automatically based on the Date_Actual (the date work was done or a receipt collected).
Let’s start with the Payroll Report - let’s assume, in this case, payroll cut of is Sunday each week. As Time records are inputted during the week, each Time record entry triggers a Date_Payroll record that has the next Sunday date automatically inputted. By the end of the week, all the Time records for that week have been coded for the Sunday cut off. On Monday morning we can issue the Payroll Report for export to .csv (ie. to Quickbooks).
Date_Actual - this is the date the Time was worked
Date_Payroll - this the date of the upcoming Sunday
For example, I worked on 2022-08-02, the next date to compile payroll is 2022-08-07
I’m thinking the formula would somehow call the date for the upcoming Sunday
First I have a Clients table that tells me the Payroll day of the week for that client. I created a Single Select Dropdown with each day of the week as an option. Then a formula field to create the day of the week number.
I like your idea of setting up the Payroll end day in the Client table and loading it through the project table - so any client project will have a payroll end day that corresponds to the client table - this payroll end day will be loaded for every project entry in the Actual table (all records in the Actual table have a Project key ie. fk_Project).