Conditional formatting on forms

Matthijs Schoots

Hello, I have a PTO request form. I am trying to get it so that if someone enters an impossible date (such as an end date before the PTO start date) that it does not allow completion but instead asks the user to confirm they mean that date. Is this possible please? cheers matthijs

Mike TV

@Matthijs Schoots

If you were to use Dynamic View, then you could put in a way (formula) to show whether the date entered caused a conflict such as the one you described and then they could edit it as they're entering their new row to the sheet.

However, with a form what you'll have to do is set up a formula which helps to determine is there's a conflict and if so then have an automation use a Request an Update action to send them an email alert advising a new date needs selected.

Amber Eakin

@Matthijs Schoots , playing off the idea from @Mike TV , you could create a simple checkbox column. You could use a formula to check the box if the Start Date is after the End Date.

=IF([Start TEST]@row > [End TEST]@row, 1)

You'll then need an Automation to run when rows are add/changed and that checkbox column is checked to send an Update Request.

Note: You'll need to make sure that you don't have Dependency Settings on (predecessors, duration, primarily used for Gantt view). If those settings are on, the sheet will change the Start Date to match the End Date because it won't allow a start date after an end date.