School Holidays in a booking system

1

Hi Coda Community,
I have to lean on your help again!

So I am making a booking system.

PROBLEM 1
I use net working days to calculate how many days a booking lasts for.

The problem is I am booking in teachers who only work during term time.
I have a seperate table that contains the term time dates.

Can I create a formula that use net working days but discounts any of the days for half term.

What I am trying to achieve is being able to calculate the total cost of a booking by counting all the booked days?

EG
A teacher is booked from September to December
They work every weekday but from Monday 26th October to Friday 30th October its half term - so they aren’t needed in school so I don’t want to count this in calculating the booked days.

PROBLEM 2
Another problem I want to solve with my booking system is that I pay teachers weekly.
If I have a booking that lasts several months, can I work out what a teacher is owed each week without having to have seperate weekly bookings for the duration of the booking?
I am working on something that looks at the week numbers during the duration of the booking but struggling to get it to work!

Thanks,
Dan

2

Dear @Dan_Price,

I can’t promise anything, but when you are able to share a dummy copy of your doc, there is a good chance that it can be fixed.

:bulb:
It’s just much more easy to work on a set structure to offer assistance

3

Thanks (yet again!)

4

Dear @Dan_Price,

Thanks for the sharing.
The school holidays starting from March 2019 aren’t listed, is this intentional?

5

No - I didnt realise!

6

Ive updated the dummy data - apologies

7

Dear @Dan_Price,

I have been playing a bit with your questions, but so far I didn’t get my head around it. I am not giving up, but it might take some time :running_man:t5:

I am sure it must be possible and let’s see if we will get a helping hand :construction_worker_man:t5: :construction_worker_woman:t5:

1 Like
8

Thanks for your efforts!

9

Hopefully someone will be able to help out.

10

Dear @Dan_Price,

I had some time to play around, to find some solutions to your question.
In the below sample “sandbox”, I tried with a simplified version to check if it’s working and it’s maybe not the most elegant way, but I think it will be workable.

In the next days I will try to find some spare to implement it on your sample and in the mean time we might get some assistance too :pray:

11

Dear @Dan_Price,

Would you mind to check if the logic and calculations are OK?
Feel comfortable to share any feedback

In the original 4th row of the bookings the start date is after the end date, would you mind to double check?

12

Thanks so much for your efforts here - this definitely looks like a workable solution!

I’m working through the logic and will update some more detailed feedback shortly!

13

Dear @Dan_Price,

I would recommend to look also to take a look to a reply with a sample from @Federico_Stefanato.

When I find some free time, I will check what learning I can take from his sample, he is much more qualified on the formulas and building logic!

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.