Expected attendance per session split into age ranges.

[Nursery Mark]

Hi,

This is my fist post on here, I have tried searching for a previous thread on this topic but cannot find on.

I am trying to create a 'simple' spreadsheet which will allow me to check how many children are going to be attending my nursery and split them into the different age bands.

The columns I am using at the moment are

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]D.o.B[/TD]
[TD]Start date[/TD]
[TD]Mon AM[/TD]
[TD]Mon PM[/TD]
[TD]Tues AM[/TD]
[TD]Tues PM[/TD]
[/TR]
[TR]
[TD]child 1[/TD]
[TD]01.08.13[/TD]
[TD]01.02.14[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]child 2[/TD]
[TD]23.03.13[/TD]
[TD]27.09.13[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]child 3[/TD]
[TD]14.09.12[/TD]
[TD]03.03.13[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]child 4[/TD]
[TD]28.06.12[/TD]
[TD]03.03.13[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]child 5[/TD]
[TD]04.01.11[/TD]
[TD]03.03.13[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]child 6[/TD]
[TD]05.07.11[/TD]
[TD]03.03.13[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]child 7[/TD]
[TD]04.10.10[/TD]
[TD]03.03.13[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]child 8[/TD]
[TD]06.01.09[/TD]
[TD]03.03.13[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]child 9[/TD]
[TD]01.02.08[/TD]
[TD]03.03.13[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Baby[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tweenie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pre-scool[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to be able to split this data into three age groups: Babies (under 2 years), Tweenies (2-3 years) and Pre-school (3+)

I would also like the formula to take account of the start date so to remove children from the list who will not have started by this data.

The idea is that I can use this as a quick look to see if I can accept another child / give me an idea of staffing needs.

Any help would be great. I know there is software out there that can do all this and more, and used to use them on previous nurseries, however this is a new start nursery which does not have the cash to pay for the software at the moment so I am looking to save myself a few hours of checking it all myself until the nursery is up and running and can afford the software which we would hopefully buy in year 2.

Cheers,

Mark.

P.s. The dates I have entered are in UK form, and I am using Excel for Mac 2011.

 

[MrBill11]

Mark,
MAybe this will help. in the D.O.B. (column B) change the format to date, insert another column (C) for age, C2=DATEDIF(B1,TODAY(),"Y"). This will give you the age in years.
B11 =COUNTIF(C2:C10,"<2") for number of babies.
B12=COUNTIF(C2:C10,">2")-COUNTIF(C2:C10"<3") for Tweenie.
B13=COUNTIF(C2:C10,">3") for Pre-School

I'm sure there is another / better way to accomplish this. I'm not following the start date part.
Hope it helps.

 

[Olddogandgrump]

Hi, The start date stumped me as well and I'm not sure that you will be able to take account of it. You might be able to use a SUMIF or COUNTIF. But the sheet in the current lay out would show a vacancy this today but not take account of the fact another child is booked in the future so that one space may be free only for a limited time. It will only be able to show you free space today.

 

[Nursery Mark]

Thanks for that. I have managed to use some of the formula given to help me out however.

The formula gives me the age in years, can I split this into years and months? so I can tell the child is 2 years and 4 months old etc.

Also when I used the formula to count children under 2 it counted all children under 2 rather than the children who were attending that session ie, those with a "1" in the column for each session.

I would like to use the start date column so that I can then set it up so that if the start date has not been reached then the box will show a "0" as in not attending, but if the start date has past then it will show up a "1" and will be counted in the numbers.

Lastly (for the time being) rather than it telling me what space is like "today" can I have a separate cell somewhere that I can type in a date in the future and see what the attendance will be on that day.

Cheers for pointing me in the right direction. thought I was quite good using excel as I knew how to type in "=sum(... rather than using the sum button. I see there is a lot more to it.

Mark.