Display a formula output that is date-dependent

Hi @AIC and welcome to the community!

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

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.

IF(
        {Age}='4',
        0.1,
IF(
        {Age}='3',
        0.125,
IF(
        {Age}='2',
        0.2
)
)
)

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

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

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

IF(
    FIND(
        'Mon',
        {Enrolment}
        ),
    {Weight}
    )

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

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…

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?

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?

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:

That’s pretty similar to an option I had considered, but that would require entering weekdays/attendance for every singe week, right?

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.

That’s what I was afraid of, but you’re probably right. It’s unfortunate that the Date field is so limiting… I’ll try this and report back. Thank you for your help!

It’s me again… I’ve been trying a different tack but can’t quite get it to work.

Instead of using an individual day field like your example, I’m thinking that if I can calculate the number of each week (i.e. the 30th week of the year, and so on) that I can group weeks that way. I’m trying something like this, but I don’t know how to have it fill in an array between the start week and the end week.

E.g. I can use this formula in the First Week column:

DATETIME_FORMAT({Enrolment Start},'W')

But I need it to automatically fill in the weeks after the first week. Let’s say for example a child starts on July 28th, which is the 30th week of the year. That child will also be there on the 31st week, 32nd, and so on until the date in the “Enrolment End” column, but I can’t figure out how to generate those “in-between” weeks between the Start and End date.

Edit: I feel like DATEADD is what I need, but I don’t know how to make this work.