Automation to Rund 28 Day Rent Cycles for each line

Micah Turner

Greetings all,

I am trying to create a workflow automation that will send me a notification every 28 days from a date in a specific cell (Start Rent). I want this to continue every 28 days until a date is listed in another cell in that row (Stop Rent). The purpose is to notify me that a Rent Cycle Bill is due for each project. Any help would be appreciated. I included a snippet of what the rows look like in my sheet.

TVang

Hi, @Micah Turner, here's another approach.

COLUMNS TO CREATE
TodayDate as a Date column type
SendNotification as a Text/Number column type

COLUMN FORMULA
SendNotification = IFERROR(MOD(TodayDate@row - [Start Rent]@row, 28), 99)

AUTOMATED WORKFLOWS
1. Record Date in TodayDate
__• Configure to run everyday.
__• Run at midnight.
__• Set the action to Record a date in TodayDate.

2. Send Notification
__• Configure to run everyday.
__• Condition1 SendNotification is equal to 0.
__• Condition2 [Stop Rent] is blank.
__• Configure your action to send the alert.

How this works…
When TodayDate@row - [Start Rent]@row equals 28 or any multiple of 28, MOD(xx,28) will be 0 (the remainder of 56÷28 is 0). Place the MOD() expression in IFERROR() to handle exceptions.

The issue with TODAY() is that TODAY() - [Start Rent]@row is not calculated until you and save the sheet. For this reason, it's a good practice to record the current day into a helper column.

Hudson_Miller

@Micah Turner

Greetings -
I think I have a solution for you, there may be a more elegant way of handling this but let me try and explain without mocking it all up for you.

1. When tackling complex formulas like this I tend to add columns to perform operations in baby steps so I can see how it all comes together. To that end I added the following columns:

Number of Days in Rental (Subtracting start date from finish)

Billing Cycles (Dividing Number of Days in Rental by 28)

Rounded # of Billing Cycles (rounding Billing Cycles up)

Bill Reminders Sent (A number column to run a Count explained later)

Last Bill Reminder Sent on (Date column)

Next Bill Reminder Date (Date Column with formula - =IF([# Bill Reminders Sent]@row < [Rounded # of billing cycles]@row, IF([Last Bill Reminder Sent on]@row = "", [Start Date]@row + 28, [Last Bill Reminder Sent on]@row + 28), "")

Send Bill Reminder (Checkbox column with formula - =IF(AND([Next Bill Reminder Date]@row <= Today(), (Today() - 28) <= [Last Bill Reminder Sent on]@row), 1, 0)

OK - so here's the explanation.

Establish the number of days in the rental period. Then divide that number by 28 to know how many bill reminders need to be sent. Round that value UP if fractional to ensure you send the final bill reminder. Once you know how many bills need to be sent we get into the formulas to trigger the send bill reminder checkbox to get checked.

You will set up automation that sends your reminder any time the field "Send Bill Reminder" gets checked. That's easy enough. End that automation with two things: a) Copy the row to a "lookup sheet" and b) change a field value and set a date for the last bill reminder sent for Today() to record the date the system sent you the reminder.

What this accomplishes is setting the last bill reminder date for the other functions to operate and puts a copy of the row into a new sheet that you can use to establish how many times that automation has run.

The field "Bill Reminders Sent" is a COUNTIF function where you count the job number or other indexing key. There would be a row for each time the automation runs for each of the bill reminders.

The rest of it is formula work - I played around with it and may have made some mistakes but it seems to work on my end logic wise.

Finally - if this sheet is infrequently opened, you will need to set up a date column and use automation to change the value in the date column if it doesn't equal today to ensue the sheet "operates" even if you aren't working in it.

Clear as mud?

Good Luck!

Hudson

Micah Turner

Thank You. That's a lot. But I'll give it a try. One issue though... we don't know what the end date will be of the rental period. The stop rent date stays blank until the customer calls to pick the scaffold up. At that point we stop rent. How do we handle that?

Hudson_Miller

@Micah Turner

How about setting up a daily automation to run if the next bill reminder due date is today? This approach is simpler but should do the trick. This gets you out of having a to keep a second sheet or care about how many reminders have been sent. I would probably put in a condition that inspects a rental status column to make sure the scaffolding is still out as a condition to ensure the reminder doesn't get sent when the rental is returned.

Micah Turner

Hmmmm. Ok. Ill try. I'm obviously way in over my head here. Thank you for the help!

TVang

Hi, @Micah Turner, here's another approach.

COLUMNS TO CREATE
TodayDate as a Date column type
SendNotification as a Text/Number column type

COLUMN FORMULA
SendNotification = IFERROR(MOD(TodayDate@row - [Start Rent]@row, 28), 99)

AUTOMATED WORKFLOWS
1. Record Date in TodayDate
__• Configure to run everyday.
__• Run at midnight.
__• Set the action to Record a date in TodayDate.

2. Send Notification
__• Configure to run everyday.
__• Condition1 SendNotification is equal to 0.
__• Condition2 [Stop Rent] is blank.
__• Configure your action to send the alert.

How this works…
When TodayDate@row - [Start Rent]@row equals 28 or any multiple of 28, MOD(xx,28) will be 0 (the remainder of 56÷28 is 0). Place the MOD() expression in IFERROR() to handle exceptions.

The issue with TODAY() is that TODAY() - [Start Rent]@row is not calculated until you and save the sheet. For this reason, it's a good practice to record the current day into a helper column.

Micah Turner

Thank you! I Entered it all as explained and ran a test. It worked on the test. I will see tomorrow morning if it triggers and sends the notification as it should. I will keep you posted!