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!