Formula to auto-populate data base on date range


With reference to the above, we are trying to do the following:

The "Date columns" (1/1/22, 2/1/22...) to capture the "10. Unit" when it matches the Start and End date.

The scenario is that we have 20 beds in the dormitory and there will be different crew who will need to stay in on different time. We have to ensure it doesn't exceed the limit. Therefore we are trying to capture the date range and the number of beds taken.

Currently, we are using another sheet to have all the dates, and to use a cross-sheet reference to say if "1/1/22 is between Start Date and End Date, then return the Unit to this cell". In doing this, we have to create a distinct reference for 365 days of the year.

Is there a better way to do this?

Appreciate any assistance or suggestion.



  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Vivien Chong ,

    Try something like this in the 01/01/22 column:

    =IF(AND(DATE(2022,01,01)>=[11. start date]@row,DATE(2022,01,01)<=[12. end date]@row),[10. Unit]@row,"")

    The (2022,01,01) will need to be changed for each column (YYYY,MM,DD). For example, the 02/01/2022 column will read =IF(AND(DATE(2022,02,01)>=[11. start date]@row,DATE(2022,02,01)<=[12. end date]@row),[10. Unit]@row,"")

    Let me know if it works for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!