Excel Forum

Excel personal budget 2024 - specify report date range

Kayla Henkeman

New Member

Members

Forum Posts: 2

Member Since:
August 29, 2024

Offline

August 29, 2024 - 8:37 am

Hello,

I'm in the process of personalising the downloaded file. It's fantastically comprehensive Smile .

My question is, is there a way to specify the date range reported on the reports tab? For instance, 25 Aug - 25 Sept?

This would be more tailored to my pay and expense cycle. A debit order that is set for 26 Aug would fall into Sept budget cycle.

Thaaank you in advance!

MS pro 2019 - Windows

See Answer

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1109

Member Since:
January 31, 2022

Offline

August 29, 2024 - 2:56 pm

The Year and Month are 'calculated' towards the end of the Data query in the Power Query section of the template.

It extracts the year and month numbers from the date without adjustments in two separate steps. If you want to treat all days after the 25th as next month's and all transactions after December 25 as next year January, you you need to replace the "Insert Year" and "Insert Month" steps by two steps adding custom columns for Year and Month.

For the Year that would be:

= if Date.Day([Date]) > 25 and Date.Month([Date]) = 12 then Date.Year([Date]) +1 else Date.Year([Date])

And for the month:

= if Date.Day([Date]) > 25 then Date.Month([Date]) + 1 else Date.Month([Date])

Now, this last step will turn the month to number 13 for dates after December 25. Couldn't think of an easy way to correct that in one step. So, you need one extra step that replaces all occurrences of 13 in the Month column with 1. That's a fairly easy.

Done all that in the attached file. See if this does what you need.