Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# Display a formula output that is date-dependent

Topic Labels: Formulas
Solved
683 12
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
6 - Interface Innovator

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.

12 Replies 12
11 - Venus

Hi @AIC and welcome to the community!

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

6 - Interface Innovator

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.

11 - Venus

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

6 - Interface Innovator

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}
)
``````
11 - Venus

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

6 - Interface Innovator

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…

11 - Venus

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?

6 - Interface Innovator

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?

11 - Venus

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:

6 - Interface Innovator

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!

6 - Interface Innovator

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.

6 - Interface Innovator

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.