Setting Invoice date from Actual date

[Jenny_Arntzen]

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).

Thanks!

[Vivid-Squid]

Hi @Jenny_Arntzen,
That is an interesting set up.

I think that you should be able to create a formula that will capture all of those parameters adjusted against the original date without the need for the Date table.

Can you provide more context on what your setting up so far?

Are you asking if a formula can automatically link records?

[Jenny_Arntzen]

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

[Vivid-Squid]

Hi @Jenny_Arntzen,
This is how I might set that up:

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.

SWITCH(
  {Payroll Day},
  'Sunday', 7,
  'Monday', 1,
  'Tuesday', 2,
  'Wednesday', 3, 
  'Thursday', 4,
  'Friday', 5,
  'Saturday', 6,
  ''
)

That screen looks like this;

Then on my Jobs table I link to the clients table and add a lookup field to get the payrollDayOfTheWeek formula results into the Jobs table.

Now add a formula field for Next Payroll Date:

IF(payrollDayLookup-WEEKDAY({Date Actual})<=0,DATEADD({Date Actual},2-(WEEKDAY({Date Actual}))+7,'days'),DATEADD({Date Actual},payrollDayLookup-(WEEKDAY({Date Actual})),'days'))

[Jenny_Arntzen]

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).

[Jenny_Arntzen]

I have a solution for the payroll date based on your suggestions but simplified to work for Sunday payroll cut off.

Here is how I did it:

1. Date_Actual - Date field

2. Date_Actual_DDD - to turn Date_Actual into Day of the Week - Name

DATETIME_FORMAT(Date_Actual, ‘ddd’)

3. DateEnd_Payroll_Sun_DIFF - to give me the difference between the day of the week and the following Sunday

SWITCH(Date_Actual_DDD,
‘Sun’, 0,
‘Mon’, 6,
‘Tue’, 5,
‘Wed’, 4,
‘Thu’, 3,
‘Fri’, 2,
‘Sat’, 1,
‘’
)

4. DAY - default integer - 1

5. DateEnd_Payroll_Sun CALC

DATEADD(Date_Actual, DateEnd_Payroll_Sun_DIFF + DAY, ‘days’)

NOTE 1 - I don’t know why I have to add the integer 1 to the calculation, but it is now returning the right date based on what has been entered in Date_Actual.

NOTE 2 - this was my first time using Switch - thank you for showing me how it could work

My next challenge is to see if I can get the 15th and the last day of the month loading from Date_Actual, depending on if the date is < 16 or > 15.