Conditional Formula based on date comparison

Hello Forum

I have a spreadsheet which is a bit like a school register that is being used for children's attendance at a school before and after school club.
The spreadsheet is in two parts. Part one shows the childs name and booking pattern. Part two shows their future attendance. Note this would normally be formatted for the whole academic year, but I've limited it to just over 1 month.

You will see the formulas in the calendar part just look back at which days of the week the child attends and for which session, AM, PM.
This works fine in most cases apart from when someones booking changes as the formulas in the calendar always reference the booking pattern.

Would it be possible to have the contents of the childs attendance frozen up to the point of the new change of booking pattern.
I've done this manually in the past using paste special, paste as values, but it does become tedious.
I can have someone attending every session of each day , maximum of ten sessions per full week. Then some time later this changes to just 2 sessions per week in the morning.

The other issue I face is for new starters. As the formulas are copied for the whole year in advance, the sheet will look fine going forward, but will show attendance in the past. I have a start date column which I'm wondering can it be used so only from the start date onwards will a formula be present for that child.

Is there anyway I can make the cell compare today's date with the corresponding date on the register and apply the necessary formula.

You could count up your morning sessions with a formula like this in AA6:

=SUMIF(Q$5:Z$5,"AM",Q6:Z6)

and the afternoon sessions with this formula in AB6:

=SUMIF(Q$5:Z$5,"PM",Q6:Z6)

Then the formulae can be copied down those columns for other children. Also, the formula from AA6:AB6 can be copied into AM6 etc for other weeks.

For new starters (and for those who move away from the school before the end of a session) you could use a letter code like "Z" for "Not expected", and just pre-fill this into the relevant days before (or after) the child starts/finishes.

If a child's attendance pattern changes, then just put 0 or 1 in the relevant days of the first week of the pattern change (maybe colour-coding these to indicate changes), and then copy the block of days across to subsequent weeks.

I would have thought that actual attendance is more important than intended attendance.

Hope this helps.

Pete

Hi Pete, thanks for this.

I will try the formulas - One note I should have mentioned is that the figures get used for invoicing purposes. Another sheet looks at this sheet and prepares in advance an invoice the following month. This is why it's the intended attendance that is important as non attendees will still be charged.

thanks
David