Expected attendance per session split into age ranges.

Nursery Mark

New Member
Mar 26, 2013

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

NameD.o.BStart dateMon AMMon PMTues AMTues PM
child 101.08.1301.02.141111
child 223.03.1327.09.131111
child 314.09.1203.03.131
child 428.06.1203.03.1311
child 504.01.1103.03.131
child 605.07.1103.03.131
child 704.10.1003.03.131
child 806.01.0903.03.1311
child 901.02.0803.03.1311


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.



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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
Upvote 0
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.
Upvote 0
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.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back