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

Matt Wood
Matt Wood ✭✭✭
edited 12/09/19 in Formulas and Functions

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. 

Smartsheet Snip.png

Tags:

Comments

  • Alejandra
    Alejandra Employee

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!