Calculating start and end dates of periods into dates into the next year

Hi,

I have a 2 tracker sheets, they both have columns for 12 periods, P1, P2 etc., rather than the names of the months as they can start at any time and run for 12 months.

First tracker sheet - The start date for the tracker can be, the first day of any month.

Second tracker sheet - The start date can be any day of the year.

I'm currently using trying the formula below to calculate the period start and end dates, (In this formula, 2 months are added to the first month and the start / end dates calculated):

Tracker start date 01/01/26, so for P3, the result of the formula is "From 01/03/26 to 31/03/26"

="From " + (DATE(YEAR([Strategic Period - Start]#) + INT((MONTH([Strategic Period - Start]#) + 0) / 12), MOD(MONTH([Strategic Period - Start]#) + 0, 12) + 2, DAY([Strategic Period - Start]#)) + "") + " to " + (DATE(YEAR([Strategic Period - Start]#) + INT((MONTH([Strategic Period - Start]#) + 0) / 12), MOD(MONTH([Strategic Period - Start]#) + 2, 12) + 1, DAY([Strategic Period - Start]#)) - 1 + "")

I'm afraid my question is the old one of what do I need to to to roll these formulae into the following year if my tracker start date is say 01/Sept/26?

I've had a couple of goes at this but getting now here fast.

Any help would be great.

Find more posts tagged with

Accepted answers

All comments

A_C

I'm thinking a couple of screen shots may be helpful with this one.

This is for a tracker with a start date of 01/May/26

Same thing, with a start date of 20/May/26

Hope this helps.

A_C

Sorry for the multiple posts…

The updated formula for start date, in this case period 2.

=(DATE(YEAR($[HC-Config Start Date]$1) + INT((MONTH($[HC-Config Start Date]$1) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$1) - 1, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$1)))

The updated formula for end date, in this case period 2.

=(DATE(YEAR($[HC-Config Start Date]$1) + INT((MONTH($[HC-Config Start Date]$1) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$1) - 0, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$1)) - 1)

Paul Newcome

Try this:

="From " + IFERROR(DATE(YEAR([Strategic Period - Start]#), MONTH([Strategic Period Start]#) + 2, DAY([Strategic Period - Start]#)), DATE(YEAR([Strategic Period - Start]#) + 1, MONTH([Strategic Period Start]#) -10, DAY([Strategic Period - Start]#))) + " to " IFERROR(DATE(YEAR([Strategic Period - Start]#) + 1, MONTH([Strategic Period - Start]#) + 1, DAY([Strategic Period - Start]#)), DATE(YEAR([Strategic Period - Start]#) + 2, 1, DAY([Strategic Period - Start]#)))

A_C

Hi Paul,

Thanks for getting back to me, I tried the formula, but it gave an unparseable error.

I'm trying it a different way to see if I can get it to work.

The formula for HC-Config Start Date is below:

=(DATE(YEAR($[HC-Config Start Date]$2) + INT((MONTH($[HC-Config Start Date]$2) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$2) - 1, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$2)))

The formula for HC-Config End Date is below:

=(DATE(YEAR($[HC-Config Start Date]$2) + INT((MONTH($[HC-Config Start Date]$2) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$2) - 0, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$2)) - 1)

It still results in the issue when moving into the next year.

As ever thanks for any help.

A_C

…as an example, for period 8 the HC-Config End Date should be 31/12/26

Paul Newcome

Are you able to provide a screenshot of the formula I provided open in the sheet as if you are about to edit it?

Make sure the fields referenced are the fields you are actually using.

In your original post, you referenced:
[Strategic Period - Start]#

In your second comment, you referenced:

$[HC-Config Start Date]$1

and

[HC-Config Data]@row

And now in your most recent comment, you are referencing:

$[HC-Config Start Date]$2

and

[HC-Config Data]@row

The formula I provided was using the same reference you used in your original post.

A_C

Sorry Paul , I added a row, so I can lock and indent all the period rows and hide them away once the solution is ready, my bad for not mentioning it.

This is how it looks at the moment.

You'll see the formulae have +0 and -0, but these aren't a problem. I'll clean them up later.

HC-Config Start Date formula for period 1 start date below

=(DATE(YEAR($[HC-Config Start Date]$2) + INT((MONTH($[HC-Config Start Date]$2) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$2) - 1, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$2)))

HC-Config End Date formula for period 1 end date below - should calc the last day of the month.

=(DATE(YEAR($[HC-Config Start Date]$2) + INT((MONTH($[HC-Config Start Date]$2) + 0) / 12), MOD(MONTH($[HC-Config Start Date]$2) - 0, 12) + [HC-Config Data]@row , DAY($[HC-Config Start Date]$2)) - 1)

Then for the HC-Period Ranges col. Also for the quarter / half year / annual rows as well.

=[HC-Config Title]@row + ": " + [HC-Config Start Date]@row + " To " + [HC-Config End Date]@row

As a bit of background,

We use this so we don't need to keep changing the column headings, we use the period ranges as the first row of our reports.

Again, thanks for any help, this is a simple one but I can't get it to work.

A_C

We use this so we don't need to keep changing the column headings, we use the period ranges as the first row of our reports.

We can have multiple calendars Annual, Financial, Seasonal (Sports) and Academic (Sports in Education).

Paul Newcome

Start Date:

=IFERROR(DATE(YEAR([HC-Config Start Date]$2), MONTH([HC-Config Start Date]$2) + [HC-Config Data]@row, DAY([HC-Config Start Date]$2)), DATE(YEAR([HC-Config Start Date]$2) + 1, MONTH([HC-Config Start Date]$2) - (12 - [HC-Config Data]@row), DAY([HC-Config Start Date]$2)))

End Date:

=IFERROR(DATE(YEAR([HC-Config Start Date]@row), MONTH([HC-Config Start Date]@row) + 1, 1), DATE(YEAR([HC-Config Start Date]@row) + 1, 1, 1)) - 1

A_C

It works, great stuff Paul.

Thanks again,

Paul Newcome

Happy to help. 👍️

A_C

I tweaked it a little bit as it was increasing the period

So the start date formula is now

=IFERROR(DATE(YEAR([HC-Config Start Date]$2), MONTH([HC-Config Start Date]$2) + ([HC-Config Data]@row - 1), DAY([HC-Config Start Date]$2)), DATE(YEAR([HC-Config Start Date]$2) + 1, MONTH([HC-Config Start Date]$2) - (12 - ([HC-Config Data]@row - 1)), DAY([HC-Config Start Date]$2)))

Thanks again,

Paul Newcome

I'm not sure I follow. Everything appears ok in your last screenshot.

A_C

Hi Paul,

The formula was adding an extra month, so I changed the Period 1 to zero, it then calculated the dates correctly, so I tweaked the formula and then set the period 1 back to 1.

This is how it looks now. So all good., as ever, you have been a great help, thanks again.

Paul Newcome

Ah. Ok. That makes sense. Glad you were able to get it sorted.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.