Smartsheet IF(AND Issue. Will not work across two separate months

Matt Wood

Hi, I've been using Smartsheet for around 6 months and would class myself as intermediate. However when it comes to Formula's I'm certainly a novice!

I have a Smartsheet that acts as a Hotel rooming list. Users can load their Hotel Check In Date and Check Out date which then prompts a formula to count up the nights that a particular guest is staying. This needs to be done as there are contracted rooms for a specific event that must be adhered to. The idea is that by having 1's populate on a given room night, this will work into a SUM formula that counts how many guests are staying across one night. Further automations then notify me when a night is oversold against the allocation.

I have this working perfectly in a separate Smartsheet, however I'm now stuck as I have an event happening across two separate months. Whatever I do with the formula, it doesn't seem to recognise the cells as they appear across two months, even though the column properties are not a 'Date', they are simply free text.

 

I have attached a screenshot which will probably be clearer than my explanation. If anyone can help, that would be brilliant. 

Alejandra

Hi mwood,

Since you're using a Text/Number column, your formula will return a zero because it's comparing the numerical values 05 and 30 to determine which one is greater. I'd recommend using date columns so that the actual dates are compared instead.