Looking for Formula Help

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.

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?