Formula for # of Room Nights omitting false data

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.

Find more posts tagged with

Accepted answers

Debbie Sawyer

When would you need a null value?

Would the logic be:

Take the Checkin date from the Checkout date, if one of the dates is missing then, return a null value?

If so, =[Check-Out Date]@row - [Check-In Date]@row will do just that anyway...

(I still don't understand the significance of comparing to a strange date of 12/31/9999 (as the dates will never match this)... Apologies if my lack of understanding is frustrating! Happy to chat via zoom if it would be easier?

Kind regards

Debbie

Debbie Sawyer

Ahh Thank you - I understand more now! :)

This should work:

=IFERROR([Check-Out Date]@row - [Check-In Date]@row,"")

The invalid operation is showing as the 12/31/9999 is a text value and not a date. So with the IFERROR statement place it will return a null entry rather than an error message.

See if this works for you.

(Thank you for sharing the screen shot - made so much more sense!)

Kind regards

Debbie

All comments

Debbie Sawyer

@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

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!

Debbie Sawyer

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

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

Debbie Sawyer

When would you need a null value?

Would the logic be:

Take the Checkin date from the Checkout date, if one of the dates is missing then, return a null value?

If so, =[Check-Out Date]@row - [Check-In Date]@row will do just that anyway...

Kind regards

Debbie

Darla Brown

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.

Debbie Sawyer

Ahh Thank you - I understand more now! :)

This should work:

=IFERROR([Check-Out Date]@row - [Check-In Date]@row,"")

The invalid operation is showing as the 12/31/9999 is a text value and not a date. So with the IFERROR statement place it will return a null entry rather than an error message.

See if this works for you.

(Thank you for sharing the screen shot - made so much more sense!)

Kind regards

Debbie

Darla Brown

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

Debbie Sawyer

@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

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

Debbie Sawyer

@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

Already done. Thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.