OTJ help please

21 months = 87 wks x 30 hrs = 2610 hrs

Holiday 5.6 x 87/52 X 30 = 281 hrs

Total hrs 2610 less holiday entitlement 281 hrs x 20% = 465.8 OTL minimum

Or total weeks 87 less holiday 8.36 = 77.64 @ 6 hrs/wk = 465.8 OTJH min

HTH

Hi Martin,

thanks for this, I worked out as below - slightly long winded

I work out 493.2 over the 21 months.

5.6 x 2=11.2 (24 months) / 24=0.47 x 21= 9.8 weeks leave over the 21 months.

92-9.8=82.2 x 6 = 493.2 hours.

Have I done it wrong?

??

I have adjusted for 92 weeks

21 months = 92 wks x 30 hrs = 2760 hrs

Holiday 5.6 x 92/52 X 30 = 297 hrs

Total hrs 2760 less holiday entitlement 297 hrs x 20% = 492.6 OTL minimum

Or total weeks 92 less holiday 9.9 = 82.1 @ 6 hrs/wk = 492.6 OTJH min

HTH

You can definitely end up with slightly different numbers, depending on if/what you round. The below may help, if you adjust the 'start date' and 'planned end date' segments to reference cells where those dates exist;

=(ROUNDUP((DATEDIF(startdate,plannedenddate,"D")/7),0)-((DATEDIF(startdate,plannedenddate,"D")*(28/365))/5))*6

Ben,

I have shown calculation as done manually, yes if you use excel you must ensure that the results match that as indicated in the ESFA template.

Agreed. Would be nice if they gave us the exact formula they use i.e., where they round up/down

Hi both,

I have used the formula above in a spreadsheet - does this look right?

Thanks L

For these dates the minimum OTJ hours for the first is 289.1 and the second 348 so they are above the minimum.

this is painful........what am I doing wrong here?

I'm trying to simplify but cannot get it right

The slight difference you've found is because the formula I added rounds certain elements up, primarily out of an abundance of caution, but as Martin has pointed out, it's not necessarily the absolute minimum. We're broadly fine with that as a provider because all of our apprentices receive many more hours than the minimum, so it's never an issue. The 'safest' way to do it though is;

• Calculate the weeks on programme using e.g., number of days / 7
• Calculate the annual leave entitlement e.g., number of days * (28/365))/5)

Then just bang the above into the ESFA template, along with 30 hours per week