Display a formula output that is date-dependent

[AIC]

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!

[Databaser]

Hi @AIC and welcome to the community!

Can you share the setup of your table and the weight formula you’re using now?

[AIC]

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.

[Databaser]

AIC:

I’m able to calculate the total weight for each weekday

Are you using a formula for this?

AIC:

I need a way where I can look at the month of January and see what the total weight will be at that time

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")

[AIC]

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}
    )

[Databaser]

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).

[AIC]

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…

[Databaser]

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?

[AIC]

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?

[Databaser]

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: