Looking for Formula Help

Options
Lori Leighton
Lori Leighton ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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.

screenshot.png

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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

    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.

  • Lori Leighton
    Lori Leighton ✭✭✭✭
    Options

    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.

    screenshot.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

    Comm.PNG

    Comm1.PNG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Nice job! yes

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!