Looking for Formula Help

Lori Leighton

In my sheet, I have two columns.  One entitled "Hotel Check-In Date" and the other "Hotel Check-Out Date".   So someone might input: Check-in 11/20/19 and out on 11/25/19.  I know have individual columns for each date:  i.e. 11/20, 11/21, 11/22, 11/23, etc.  

I am looking for a formula that will be a value of "1" in the correct column based on the Check-in and Check-out dates.   I then have a calculation of how many hotel rooms I will need to book for 11/20, 11/21 and so forth.

Any suggestions.   the formulas I have tried do not return the values correctly.

Andrée Starå

Hi Lori,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Lori Leighton

Here is a screenshot of what I am trying to automate.  Hope this explains it better.

On the 1st entry, I need the # 1 to go on 11/22.

On the 2nd entry, I need the  #1 to go on 11/20, 11/21 and 11/22.    

The number 1 does not go on the last date because they are checking out of the hotel that day.  The value only needs to go when a person is spending the night.

Paul Newcome

There are a few different solutions. The easiest way to go about doing this (in my opinion) is to add in 4 additional rows. Make rows 2, 3, and 4 child rows of row 1.

 

We will use these rows to automate a DATE function which will be compared to the dates listed to determine whether or not a 1 should be input into the cell.

 

The DATE function looks like this:

 

=DATE(yyyy, mm, dd)

 

So we will use row 2 as the year, row 3 as the month, and row 4 as the day.

 

Row 1 will combine these  to make them LOOK like a date just for making the sheet look a little cleaner and rows 2, 3, and 4 can then be collapsed and hidden under the parent row 1.

 

In rows 2, 3, and 4, enter the appropriate values for the corresponding date in the column header.

 

In the row where you want to populate a 1, you would use something similar to this (I will use column names from your screenshots, but you can adjust as needed).

 

=IF(AND($[Hotel Check-In]@row <= DATE([11/17]$2, [11/17]$3, [11/17]$4), $[Hotel Check-Out]@row > DATE([11/17]$2, [11/17]$3, [11/17]$4)), 1)

.

The above will provide a solution.

 

Below is converting the numbers from rows 2, 3, and 4 into what appears to be a date into row 1.

 

=[11/17]3 + "/" + [11/17]4 + "/" + [11/17]2

 

This will provide a text string that replicates

 

mm/dd/yyyy

.

Below are a few screenshots. First screenshot shows everything. Second screenshot shows rows 2, 3, and 4 collapsed.

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Andrée Starå

Nice job!