Calculating bed requirements in a camp

[ejsdon]

Hello All.

I am trying to build a sheet that will automatically populate a separate tab in a countable numbers format. See the attached sheet.

On "Personnel Details" tab I have dates in a conventional format in columns G and H.

I would like the time between and including these dates to be represented on the "Bed Requirement" tab with a 1 for those positions with a 'Single' 'Room Type' and a 0.5 for those with a 'Shared' 'Room Type'.

Any help appreciated, I am struggling to find a way to represent the standard calendar dates of the first tab in the cells of the second tab as values 1 or 0.5.

[Olly]

Enter dates in E3:BM3 rather than just numbers, then you can use this in E4:BM8;

Please Login or Register  to view this content.

[ejsdon]

Thank You Olly, worked a treat.

[ejsdon]

Ok, this was SOLVED initially by Olly - and Thank You for that.

Now I have a further issue.

If on a separate tab entirely (on the attached sheet as 'Movement') we have the list of names - and a sequence of dates in a row - some of which are in the past, some in the future - I want my dates on the 'Personnel Details' tab to be populated from here (columns I to R).

Note:
- I want to disregard any pair of dates where the 'Date Out' value is in the past.
- I want to go forward up to 6 months.