# Formula for # of Room Nights omitting false data

Options
Overachievers

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

• ✭✭✭✭✭✭
Options

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?

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

• 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

• ✭✭✭✭✭✭
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...

• 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

• 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

• Overachievers
Options

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

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

Phew!! We got there! ha ha

Thank you for sticking in there with me!

Have fun with Smartsheet :)

Kind regards

Debbie

• 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

• ✭✭✭✭✭✭
Options

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 :)

• Overachievers
Options