Aug 03, 2021 11:11 AM
Hi, I’m trying to set up a sheet to manage attendance for a preschool center. Kids have a different “weight” depending on their age, which I successfully set up. The weighting is used to determine how many kids we can have at once.
What I now need to do is show a daily breakdown of those weights, because some children don’t attend preschool every day. I need a calendar where I can look at any given day and see how many total kids will be there.
I’m trying to combine the weight formula with START and END date fields, but can’t make it work… Any advice is much appreciated!
Solved! Go to Solution.
Aug 16, 2021 12:11 PM
Here’s what I ended up doing… Not a pretty solution but it works.
I created a view for each “week number” and then added two formula columns:
WEEKNUM({Enrolment Start})
WEEKNUM({Enrolment End})
I then use a filter for each view to display only those that exist within that range.
Aug 04, 2021 10:08 AM
Hi @AIC and welcome to the community!
Can you share the setup of your table and the weight formula you’re using now?
Aug 04, 2021 10:29 AM
Absolutely, thanks! Here’s the weighting formula:
IF(
{Age}='4',
0.1,
IF(
{Age}='3',
0.125,
IF(
{Age}='2',
0.2
)
)
)
The layout is basically:
Child Name Weight Enrolment Start Date End Date
Bobby 0.2 Tue, Thu Jan 3 2022 June 30 2022
Jimmy 0.1 Wed, Thu, Fri Sept 2 2021 June 30 2022
I’m able to calculate the total weight for each weekday, but the problem is if a child only starts later in the year (Bobby in this example). I need a way where I can look at the month of January and see what the total weight will be at that time, otherwise we could exceed the maximum allowed weight.
Aug 04, 2021 02:47 PM
Are you using a formula for this?
You could make a formula to see if the current month is equal to or between the start and end date. Based upon a “true” or “false” result, you could then use a simple view to show all the children that (will) attend in this month. In that view, you could see the summary of their weight in the summary bar beneath all the records.
Something like:
IF( AND( OR( IS_SAME( DATETIME_PARSE( TODAY()&"", "YY-M"), DATETIME_PARSE( {Start Date}&"", "YY-M")), IS_AFTER( DATETIME_PARSE( TODAY()&"", "YY-M"), DATETIME_PARSE( {Start Date}&"", "YY-M"))), OR( IS_BEFORE(DATETIME_PARSE(TODAY()&"", "YY-M"), DATETIME_PARSE({End date}&"", "YY-M")), IS_SAME(DATETIME_PARSE(TODAY()&"", "YY-M"), DATETIME_PARSE({End date}&"", "YY-M")))), "true", "false")
Aug 04, 2021 03:11 PM
Hi, yes I currently have a column for each weekday to check enrolment (formula below). With your method should I update each of these to take into account a date range, or would I create a column for each month?
IF(
FIND(
'Mon',
{Enrolment}
),
{Weight}
)
Aug 04, 2021 03:22 PM
Hmm, you could go more specific and look at the day (“YY-MM-DD”) instead of the month (“YY-M”) and via the formula see if that’s on or between the start and ending date, AND combine that with an adapted version of your find formula, where you look at what day today is in comparison with the enrolled days (“ddd” = Day of the week, three-letter abbreviation = Sun Mon … Fri Sat).
Aug 04, 2021 03:32 PM
How would the user select a date range to display in that case? When I originally started out with this I thought I could output a number into the Calendar view, but the calendar is unfortunately very limiting…
Aug 05, 2021 12:21 AM
I see your wish list is growing :grinning_face_with_sweat: If you want to select a date range and then see the total weight for the individual days in that range and have it shown on the calendar view… I don’t think it’s possible with the native Airtable functionality. But scripting seems to solve everything, so maybe @kuovonne could see the possibilities here?
Aug 05, 2021 04:49 AM
Ha, well if I have to use columns that’s fine too!
Databaser
August 5
I see your wish list is growing :grinning_face_with_sweat: If you want to select a date range and then see the total weight for the individual days in that range and have it shown on the calendar view… I don’t think it’s possible with the native Airtable functionality. But scripting seems to solve everything, so maybe @kuovonne could see the possibilities here?
Aug 05, 2021 07:13 AM
I think the challenge is that, if you want a date range on day level (eg from 1/08 to 31/08), possibly with kids coming on different days in different weeks, you’re input will also have to be on that level.
Maybe you could make a record per child per week and have 5 fields (eg checkbox) to indicates who comes when. Then you can link every record to a child (other table) and use a lookup for their weight. You can then calculate (via formula field) the weight per day and group everything per week. You can use a filter to add a date range (eg all the weeks between 1/09 and 31/10).
Maybe not the most efficient way, but it should get the job done :man_shrugging: