Need help with creating formula/function to determine asset availability

NishaTKD

Hi All! This is what I am trying to achieve:

I would like to add a function or formula in 'Availability' column to determine if the Vessel(s) selected in 'Vessel Type' column are available or not for newly 'Submitted' rows.

Condition to check availability: Compare Start Date and End Date for Vessel(s) in 'Submitted' with Start and End Date of those Vessels with 'Scheduled' Status:

  - If date range for Submitted vessels overlap with date range with previous records with Scheduled status - then Not Available

- If Date range is outside the date range of previous records with Scheduled status - then Available

- If Start Date for vessels in 'Submitted' row is equal to or greater than the 'End date' on 'Scheduled' row, then Available.  

I have attached an example of my data. I would also like to know if this can be achieved using Automation workflow. I am open to adding news columns. Thanks!

Bassam Khalil

Hi @NishaTKD

Please try the following :

1- First, you'll need to add some helper columns to assist in checking the availability based on the conditions you described. These columns will help in simplifying the primary formula you'll use in the 'Availability' column.

Helper Columns to Add:

• Scheduled Start Date
• Scheduled End Date
• Overlap Check

These columns will store the start and end dates of scheduled vessels and will help in determining if there is an overlap.

2- Formulas to Implement:

• Scheduled Start Date: This will pull the earliest start date from the scheduled records that match the vessel type.

=MIN(COLLECT([Start Date]@row, [Vessel Type]@row, [Status]@row, "Scheduled"))

Scheduled End Date: This will pull the latest end date from the scheduled records that match the vessel type.

=MAX(COLLECT([End Date]@row, [Vessel Type]@row, [Status]@row, "Scheduled"))

Overlap Check: This formula checks if there's an overlap based on the collected dates.

=IF(AND([Start Date]@row <= [Scheduled End Date]@row, [End Date]@row >= [Scheduled Start Date]@row),
 "Not Available", "Available")

3- If you want to automate the process of checking and updating the availability status when a new submission is made, you can set up an Automation workflow in Smartsheet:

Steps to Set Up Automation:

• Trigger: Set the trigger to when a new row is added or when the 'Submitted' column is updated.
• Action: The action should be to run a workflow that checks the vessel availability using the formulas set up in the helper columns and then updates the 'Availability' column accordingly.