Creating a Booking System with a Time Component
Hi everyone,
I have the following asset booking system and received feedback that I should add a time component to it:
Unfortunately, I haven't been able to get it to work correctly as, in the above screenshot, you can see that the booker is stating the asset is available when it is not. Additionally, the 'Assignee in Possession Before Your Booking Date:' column is not functioning correctly anymore.
My formulas for the columns are as follows:
Asset Available During Your Booking Date Range?
=IF([Asset Booking End Time:]@row = "", "", IF([Asset Booking End Date:]@row <> "", IF(COUNTIFS([Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking Start Date:]:[Asset Booking Start Date:], @cell <= [Asset Booking End Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell >= [Asset Booking Start Date:]@row, [Asset Booking Start Time Converter:]:[Asset Booking Start Time Converter:], @cell <= [Asset Booking End Time Converter:]@row, [Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], @cell >= [Asset Booking Start Time Converter:]@row, [Asset Booking Priority Rank:]:[Asset Booking Priority Rank:], @cell < [Asset Booking Priority Rank:]@row) > 0, "NOT AVAILABLE", "AVAILABLE")))
Assignee in Possession Before Your Booking Date:
=IF([Asset Available During Your Booking Date Range?]@row <> "AVAILABLE", "", IF([Nearest Booking Date:]@row <> "", INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Nearest Booking Date:]@row, [Nearest Booking Time:]:[Nearest Booking Time:], @cell < [Nearest Booking Time:]@row), 1), "NO PREVIOUS ASSIGNEE(S)"))
I have the following helper columns:
Asset Booking Priority Rank:
This is an auto-number column
Nearest Booking Date:
=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], @cell <= [Asset Booking Start Date:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
Nearest Booking Time:
=MAX(COLLECT([Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], [Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], @cell < [Asset Booking Start Time Converter:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
I've run into a problem with the above formula as it returns '0' if it does not find a match -- which is problematic in 24 hour time as midnight is typically 0, so in the time converter columns below, I've assigned midnight a value of 10 to get around this.
The Asset Booking Time Converter columns just use the following IF statements (as Smartsheet does not appear to accept time entries, I've assigned the various time increments a 24 hour numeric value), as the formulas are really long, I've just posted the formula for one of the columns below:
=IF([Asset Booking Start Time:]@row = "12:00 AM", 10, IF([Asset Booking Start Time:]@row = "12:30 AM", 30, IF([Asset Booking Start Time:]@row = "1:00 AM", 100, IF([Asset Booking Start Time:]@row = "1:30 AM", 130, IF([Asset Booking Start Time:]@row = "2:00 AM", 200, IF([Asset Booking Start Time:]@row = "2:30 AM", 230, IF([Asset Booking Start Time:]@row = "3:00 AM", 300, IF([Asset Booking Start Time:]@row = "3:30 AM", 330, IF([Asset Booking Start Time:]@row = "4:00 AM", 400, IF([Asset Booking Start Time:]@row = "4:30 AM", 430, IF([Asset Booking Start Time:]@row = "5:00 AM", 500, IF([Asset Booking Start Time:]@row = "5:30 AM", 530, IF([Asset Booking Start Time:]@row = "6:00 AM", 600, IF([Asset Booking Start Time:]@row = "6:30 AM", 630, IF([Asset Booking Start Time:]@row = "7:00 AM", 700, IF([Asset Booking Start Time:]@row = "7:30 AM", 730, IF([Asset Booking Start Time:]@row = "8:00 AM", 800, IF([Asset Booking Start Time:]@row = "8:30 AM", 830, IF([Asset Booking Start Time:]@row = "9:00 AM", 900, IF([Asset Booking Start Time:]@row = "9:30 AM", 930, IF([Asset Booking Start Time:]@row = "10:00 AM", 1000, IF([Asset Booking Start Time:]@row = "10:30 AM", 1030, IF([Asset Booking Start Time:]@row = "11:00 AM", 1100, IF([Asset Booking Start Time:]@row = "11:30 AM", 1130, IF([Asset Booking Start Time:]@row = "12:00 PM", 1200, IF([Asset Booking Start Time:]@row = "12:30 PM", 1230, IF([Asset Booking Start Time:]@row = "1:00 PM", 1300, IF([Asset Booking Start Time:]@row = "1:30 PM", 1330, IF([Asset Booking Start Time:]@row = "2:00 PM", 1400, IF([Asset Booking Start Time:]@row = "2:30 PM", 1430, IF([Asset Booking Start Time:]@row = "3:00 PM", 1500, IF([Asset Booking Start Time:]@row = "3:30 PM", 1530, IF([Asset Booking Start Time:]@row = "4:00 PM", 1600, IF([Asset Booking Start Time:]@row = "4:30 PM", 1630, IF([Asset Booking Start Time:]@row = "5:00 PM", 1700, IF([Asset Booking Start Time:]@row = "5:30 PM", 1730, IF([Asset Booking Start Time:]@row = "6:00 PM", 1800, IF([Asset Booking Start Time:]@row = "6:30 PM", 1830, IF([Asset Booking Start Time:]@row = "7:00 PM", 1900, IF([Asset Booking Start Time:]@row = "7:30 PM", 1930, IF([Asset Booking Start Time:]@row = "8:00 PM", 2000, IF([Asset Booking Start Time:]@row = "8:30 PM", 2030, IF([Asset Booking Start Time:]@row = "9:00 PM", 2100, IF([Asset Booking Start Time:]@row = "9:30 PM", 2130, IF([Asset Booking Start Time:]@row = "10:00 PM", 2200, IF([Asset Booking Start Time:]@row = "10:30 PM", 2230, IF([Asset Booking Start Time:]@row = "11:00 PM", 2300, IF([Asset Booking Start Time:]@row = "11:30 PM", 2330, ""))))))))))))))))))))))))))))))))))))))))))))))))
Any help is greatly appreciated, thank you!
Answers
-
Very frustrating that this is not available as an option - I created a Training Session Request Form -and need to be able to have a Start Time - End Time and Timezone selection for the requestor - we have a calendar option - why not a time of day option? makes zero sense...
-
Hi Kim,
It's definitely frustrating -- I looked into other options and Smartsheet seems to be pushing third party solutions that are pretty costly for a small business.
Hopefully the formulas I posted above in my question can help you out -- regarding the timezone component of it, I think you might have to create a hidden helper column where it converts the times entered by the user into a standardized time zone (e.g., Greenwich Mean Time (GMT)) using an IF statement (the IF statement is going to be ridiculously long but should get the job done).
For me, since I only have to deal with one time zone, my IF statement just converts to a numerical value (24 hour time without the colon -- e.g., 3:00 PM = 1500)
Best,
James
Help Article Resources
Categories
Check out the Formula Handbook template!
