Calculate days in school using term time dates

[Jordan20]

Hi all,

I am trying to build an Annual Leave Entitlement and Pay Calculator for colleagues who work Term Time Only. I have the basic setup of the calculator which works for full-time colleagues. As these colleagues may be working part-time or full-time BUT only when the pupils are in school, I need your help to build a worksheet which calculates the number of days pupils are in school.

I have created a worksheet where the Manager can input the term dates of the school, namely "Term Days Calculator". I have been able to build this worksheet to calculate the number of days that the pupils are in school during the school year. The problem however arises when the school year (which runs September to July) needs to be converted into the holiday year (which runs 1st April to 31st March).

I wanted to use the "Term Days Calculator" worksheet to build a full calendar of dates from 1st April to 31st March which will pull through data to a different worksheet, namely "TDC Calculations" to populate the "Type" of day, e.g. "School Day"; "School Holiday", "Weekend", or "Bank Holiday".

Please find screenshots below.


Thank you all in advance.

 

[MrSyyr]

Hi,
This is perfectly possible, but I think you need a list of Start of Term Dates, Half Term Begins Dates, etc. preferably in a table with six columns for each of these date types. so each row in the table would be a school term. Then you need a separate table with a list of Bank holidays. Something like this:

I've made it in to a table (Select the data, go to insert Tab and click table) and called them t_Dates and t_BankHols (you can set the table name on the left, if you select the table and go to design tab). Naming them makes it easier to refer to.
Then the day type can be determined by something like this (B4 being the first date in your sheet)

=IF(WEEKDAY('TDC Calculations'!B4,2)>5,"W",IF(COUNTIF(t_BankHols[Bank Holidays],'TDC Calculations'!B4)=1,"B",IF(COUNTIF(t_Dates[[Half Term Begins]:[Half Term Begins]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Half Term Ends]:[Half Term Ends]],"<"&'TDC Calculations'!B4)+1,"HT",IF(COUNTIF(t_Dates[[Start of Term]:[Start of Term]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Term Ends]:[Term Ends]],"<"&'TDC Calculations'!B4)+1,"T",IF(COUNTIF(t_Dates[[Holidays Begin]:[Holidays Begin]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Holidays End]:[Holidays End]],"<"&'TDC Calculations'!B4)+1,"Hol","Error")))))

This perhaps looks horrendous, but it seems to work. Perhaps the best way to use it is this:
1. select the D4 cell in your TDC Calculations tab
2. Click on formulas tab, Name Manager > New...
3. Give it a Name of DayType
4. Copy the horrible formula above in the RefersTo bit and click OK.
5. Put =DayType in the cells that you want the day type in.
I hope that's usable. I can explain how it works if you like.

 

[Jordan20]

Hi,
This is perfectly possible, but I think you need a list of Start of Term Dates, Half Term Begins Dates, etc. preferably in a table with six columns for each of these date types. so each row in the table would be a school term. Then you need a separate table with a list of Bank holidays. Something like this:
View attachment 12723
I've made it in to a table (Select the data, go to insert Tab and click table) and called them t_Dates and t_BankHols (you can set the table name on the left, if you select the table and go to design tab). Naming them makes it easier to refer to.
Then the day type can be determined by something like this (B4 being the first date in your sheet)

=IF(WEEKDAY('TDC Calculations'!B4,2)>5,"W",IF(COUNTIF(t_BankHols[Bank Holidays],'TDC Calculations'!B4)=1,"B",IF(COUNTIF(t_Dates[[Half Term Begins]:[Half Term Begins]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Half Term Ends]:[Half Term Ends]],"<"&'TDC Calculations'!B4)+1,"HT",IF(COUNTIF(t_Dates[[Start of Term]:[Start of Term]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Term Ends]:[Term Ends]],"<"&'TDC Calculations'!B4)+1,"T",IF(COUNTIF(t_Dates[[Holidays Begin]:[Holidays Begin]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Holidays End]:[Holidays End]],"<"&'TDC Calculations'!B4)+1,"Hol","Error")))))

This perhaps looks horrendous, but it seems to work. Perhaps the best way to use it is this:
1. select the D4 cell in your TDC Calculations tab
2. Click on formulas tab, Name Manager > New...
3. Give it a Name of DayType
4. Copy the horrible formula above in the RefersTo bit and click OK.
5. Put =DayType in the cells that you want the day type in.
I hope that's usable. I can explain how it works if you like.

Hi MrSyyr,
Thank you very much for your response and writing this formula. I have been playing about with this and indeed it works - it is brilliant. I really appreciate your help, thank you I have two questions/further points:

1) Do you know if Excel can somehow automatically recognise Bank Holidays in a calendar; as the year changes year on year?
2) How can I build another table into the formula? I need to allocate "Inset Days" (Teacher Training) into this. I had a play with the formula and came up with:
IF(COUNTIF(t_InsetDays[Inset Days],'TDC Calculations'!B23)=1,"Inset Day"
However, this has caused a problem as a date that isn't in any of the tables now returns "Inset Day" rather than "Error".

Please let me know if you have any thoughts?

Thank you in advance.

 

[MrSyyr]

This puts the inset days ahead of everything except the weekends, so they will show if on a bank holiday, but not on a Saturday!
=IF(WEEKDAY('TDC Calculations'!B4,2)>5,"W",IF(COUNTIF(t_InsetDays[Inset Days],'TDC Calculations'!B4)=1,"Inset Day",IF(COUNTIF(t_BankHols[Bank Holidays],'TDC Calculations'!B4)=1,"B",IF(COUNTIF(t_Dates[[Half Term Begins]:[Half Term Begins]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Half Term Ends]:[Half Term Ends]],"<"&'TDC Calculations'!B4)+1,"HT",IF(COUNTIF(t_Dates[[Start of Term]:[Start of Term]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Term Ends]:[Term Ends]],"<"&'TDC Calculations'!B4)+1,"T",IF(COUNTIF(t_Dates[[Holidays Begin]:[Holidays Begin]],"<="&'TDC Calculations'!B4)=COUNTIF(t_Dates[[Holidays End]:[Holidays End]],"<"&'TDC Calculations'!B4)+1,"Hol","Error"))))))

 

[MrSyyr]

Re Bank holidays, I would import them from the gov web site and then input them manually.
The government changes them once in a while, so while it it possible in theory to calculate bank holidays, I suggest that solution.
Select the cell where you want the data to go (it will be put to the right and below this point).
Data Tab > From web (if you get the Script error box, just click no, to stop scripts running)
put UK bank holidays in the Address box and click Go
Select the little arrows next to the tables that you want to import. I did it for the first two and got this

You can refresh this at any time by selecting a cell in the table and pressing Alt + F5

If you need to change the tables that are input, then select a cell in the table, click Data Tab > Connections > Properties > Definition Tab > Edit Query and change the selection
I added the next two and then clicked Import > OK > Refresh > Close and got this

It's not the most convenient format, but it is in a place that should retain a consistent format, unlike a lot of other sites.
Good Enough?

 

[Jordan20]

Re Bank holidays, I would import them from the gov web site and then input them manually.
The government changes them once in a while, so while it it possible in theory to calculate bank holidays, I suggest that solution.
Select the cell where you want the data to go (it will be put to the right and below this point).
Data Tab > From web (if you get the Script error box, just click no, to stop scripts running)
put UK bank holidays in the Address box and click Go
Select the little arrows next to the tables that you want to import. I did it for the first two and got this
View attachment 12792
You can refresh this at any time by selecting a cell in the table and pressing Alt + F5

If you need to change the tables that are input, then select a cell in the table, click Data Tab > Connections > Properties > Definition Tab > Edit Query and change the selection
I added the next two and then clicked Import > OK > Refresh > Close and got this
View attachment 12793
It's not the most convenient format, but it is in a place that should retain a consistent format, unlike a lot of other sites.
Good Enough?

MrSyyr,
Thank you so much for your help and so sorry for my delayed response and acknowledgement, other work took priority. I have decided to go with the manual checking of Bank Holidays. The formula for DayType is working so thanks again for this. However, I'd now like to be able to use this (or ideally another formula in addition) to determine whether or not a Bank Holiday falls within the Term time (or falls outside of the Half Term and Holidays).

I've been playing around but don't fully understand how the DayType formula works so unfortunately I have not been able to come up with anything which works and hoped you might be willing to help me out again please?