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!

[AIC]

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!

[AIC]

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.

[AIC · Accepted solution]

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.