Need help with creating formula/function to determine asset availability
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!
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!