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.
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Nice job!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!