Forum Discussion
Help with a Form and Formulas
So IF you can make a few assumptions that calculation should work. In your case your dates were 6th - 31st which starts on a Mon and ends on a Fri and was 20 days. So just divide by 5 to get 4 weeks and multiply by the weekly totals. BUT that assumes a) no weekday holidays, b) the start and end dates result in exact weeks. Otherwise you must either do it day by day or account for partial weeks at the start and end and any holidays inbetween.
As for the 2nd issue, your setup really won't create a roster as you have it. As soon as the next parent changes the values the 'roster' would also get changed/removed. So I would recommend instead having that 'Name of Child' filed go BELOW the pricing and restructured as a 'Sign Up List' so you have all the information for a child on a single line. So the 'simpliest' might be:
Child Name, Breakfast Monday, Breakfast Tues, .... , AfterSchool A Monday, AfterSchool A Tues, .... AfterSchool B Mon, AfterSchool B Tue, ...
where A and B are the 2 different time options
Alternatively you could make it easier to enter but make the calculations a little more tricky:
Chlid Name, M, T, W, Th, F
and undeer each day the options would be None, Breakfast only, AfterSchool A only, AfterSchool B only, Breakfast + AfterSchool A, Breakfast + AfterSchool B
then to make calculations easier I would create a table on the side (or in a Named Lamda Function) to convert those options to True/False valuse for Breakfast, AfterSchool A, AfterSchool B. So a table might look like:
Option Breakfast, AfterSchool A, AfterSchool B
None, 0 0 0
Breakfast only, 1 0 0
AfterSchool A only, 0 1 0
AfterSchool B only, 0 0 1
Breakfast + AfterSchool A, 1 1 0
Breakfast + AfterSchool B 1 0 1 and then you can use XLOOKUP (or VLOOKUP) to get out the corresponding values
Finally all this said, you need to consider that there is NO security here and you need to consider if there is any privacy concerns and issues with parents making mistakes that would affect other student information. So parent A lists Billy for x, y, z
then parent B goes on the list and accidentally deletes y or adds something or changes something on Billy's line.
So although you can do this I have to question the value of it and potential issues with it.