Formula to auto-populate data base on date range

Vivien Chong

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.

Thanks.

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!

Best,

Heather