Formula for # of Room Nights omitting false data

Options

Hi,

I have this formula in excel that I am trying to recreate in SmartSheet:

=IF(OR(H7815=$D$1,I7815=$D$1),"",DATEDIF(H7815,I7815,"D"))

Criteria is Check-In Date and Check-Out Date. The $D$1 is 12/31/9999 which I want to count as 1 night. When that date appears it is in both columns so it produces an Invalid Operation.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

Best Answers

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Darla Brown

    Is there any chance you could explain your scenario a little more?

    I take it you are not just trying to work out the number of nights between Check In-Date and Check-Out Date?

    What is the significance of 12/31/9999 in this function?

    The way I am reading your function (please forgive me for not understanding it) is:

    If the Check-In Date is equal to 12/31/9999 or the Check-Out Date is equal to 12/31/9999 then return a null value otherwise find the Date Difference in Days between the Check-In Date and the Check-Out Date.

    Is that what you want it to do?

    I'd love to try and help - but at the moment I'm not understanding why you can't just do a simple =[Check-Out Date]@row - [Check-In Date]@row!

    Kind regards

    Debbie

  • Darla Brown
    Darla Brown Overachievers
    Options

    Yes, you are reading that correctly. I did try the simple =[Check-Out Date]@row - [Check-In Date]@row function but the 12/31/9999 threw in the Invalid Operation error.

    I don't know why this date shows up except it may be when the employee rate is used for that stay. Not really sure. In my excel column, it still counts it as 1 night. I inherited this excel document and I've been slowly migrating to SS. The file is 30 mb with very complicated formulas all throughout. It's been a daunting task!

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    Are you sure that both your Check-In Date column and your Check-Out Date columns are in Date Datatype and not Text Number?

    Sometimes when we import data from Excel the data types don't always set correctly in the Smartsheet end...

  • Darla Brown
    Darla Brown Overachievers
    Options

    Yes. They are both in Date type. How can I return the null value using the simple formula?

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Darla Brown
    Darla Brown Overachievers
    Options

    It's not frustrating. I understand when you can't see it's difficult. Here is a a snipit of what I'm looking at when I apply the simple rule. This data is imported from Amex website in csv form. So these are their dates. I have no control, but do not want to manipulate data, just report it.


    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Darla Brown
    Darla Brown Overachievers
    Options

    YES!! That worked. Thank you so much!!

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Darla Brown

    Phew!! We got there! ha ha

    Thank you for sticking in there with me!

    Have fun with Smartsheet :)

    Kind regards

    Debbie

  • Darla Brown
    Darla Brown Overachievers
    Options

    LOL! No Worries! Thanks for helping me figure it out.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Darla Brown

    You're welcome.

    If you haven't already done so, could you flag the part of this thread with the final answer in it, so that the community know you have a result to your query. Thanks Darla. Have a good rest of the day :)

  • Darla Brown
    Darla Brown Overachievers
    Options

    Already done. Thanks!

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!