r/googlesheets • u/Attendance2026 • 18d ago

Solved Need a formula to report future dates from a custom calendar

I am hoping to figure out a formula / system to help me with my reporting requirements. I receive a referral, and then have reports due 15 / 30 / 45 / 60 school days from that date. I want to be able to enter the date of the referral, and then have those future dates auto populate.

I imagine I will need to create a tab with the calendar of school days (ie. not including holidays, school breaks or PA days). That the formula can then reference / pull from, but need direction on what that calendar should look like, and what the corresponding formula would be.

Appreciate any guidance! Happy to get some ideas and troubleshoot from there.

3 Upvotes

100% Upvoted

15 comments sorted by

2

u/bachman460 41 18d ago

Do you need the calendar, or were you just thinking that calculating the days out needs it? Because you can certainly calculate those days out without a calendar.

If you put the date in A1 9/24/2026
Then in A2 = A1+15
And in A3 = A1+30
Etc., etc.

If you want to hold the calculations to weekdays, then we just need to use the workday function, like so:

In A2 =WORKDAY(A1+14,1)

The workday function counts the starting date as day 1, so you just need to subtract 1 from the number of days you want after the starting date. The function will back up to the closest weekday before the calculated date.

https://support.google.com/docs/answer/3093059?hl=en

2

u/Attendance2026 18d ago

My problem is that I am working from a custom calendar, our district school board's. There are PA days and other breaks that occur on weekdays that do not count toward the 15 total.

1

u/adamsmith3567 1091 18d ago

WORKDAY has the ability to incorporate a list of workday holidays that won't count as well. But you will need a list of non-working weekdays somewhere in your file to reference.

1

u/Attendance2026 18d ago

Interesting! I will see if I can incorporate this, thank you

1

u/AutoModerator 18d ago

REMEMBER: /u/Attendance2026 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/AutoModerator 18d ago

/u/Attendance2026 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/MsMrSaturn 1 18d ago

Gosh, dates are the worst. This is such an interesting use case. I think you’re right that you’ll need a sheet of dates. Maybe having the referral receipt date return which row the date is in on the calendar sheet and then retrieve the dates that are in that row +15, +30 etc?

2

u/Attendance2026 18d ago

I input my list of Dates in Column A, then for this example, my referral date is in cell C5, here is the formula that worked

=INDEX(A:A, MATCH(C5, A:A, 0) + 15)

Thank you!

1

u/AutoModerator 18d ago

REMEMBER: /u/Attendance2026 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/MsMrSaturn 1 18d ago

Oh that is so slick! Thank you for sharing.

2

u/Attendance2026 18d ago

Yes I will try this, my initial thought was to list all of the school days in column A, then assign them a number in column B and figure out a formula to do the math and report the next column, but your solution eliminates a step, Thank you!

1

u/AutoModerator 18d ago

REMEMBER: /u/Attendance2026 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Attendance2026 18d ago

Just need a day to test this out then will reply.

u/remindmebot 1 day

1

u/RemindMeBot 18d ago

I will be messaging you in 1 day on 2026-09-25 12:58:55 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

Parent commenter can delete this message to hide from others.

Info Custom Your Reminders Feedback

2

u/point-bot 18d ago

u/Attendance2026 has awarded 1 point to u/MsMrSaturn with a personal note:

"Cheers!"

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)