Issue with calendar month in formula - need a specific date range

[rmase]

Hello

I'm using the attached finance manager template I found online to track all my income/expenditure. The report on the second sheet pulls through some analysis based on the transactions I enter on the first sheet. When I select a calendar month in H3, the report is currently configured to display the figures for all the transactions in that given month.

However, if you refer to the transactions sheet, you'll see in column J that the month doesn't always correspond with the actual month for the transaction dates in column A (see rows 14-18). That's because I track my finances based on my pay period - which is 23rd to 22nd of the following month. So all transactions that occur between 23 August and 22 September will be marked as 'August' in column J on the transactions sheet; all transactions between 23 September and 22 October will be marked as 'September'; and so forth.

I would like the report on the second sheet to reflect my pay period as oppose to calendar months. So for example, when I select August in H3 on the report, I would like the spreadsheet to pull through all figures marked as 'August' on the transactions sheet in column J (regardless of what date they actually occurred).

I hope that makes sense and would appreciate any help with this.

Thanks

[Aardigspook]

The date range which your Report sheet is searching is defined in all the formula by these:

Start date: ">"&EOMONTH(I_CHOSEN_MONTH,-1)
End date: "<="&I_CHOSEN_MONTH

I_CHOSEN_MONTH is the date in H3, which in your sample sheet is 30 Sep 2020. The start/end dates above therefore translate as 'the end of the month before the chosen date' and 'the chosen date'.

Hence what you need to do is amend those parts of the formulae to look at what you want to be the start and end dates.

1. Select everything on the Report sheet.
2. Go to 'Find and Replace' (keyboard shortcut Ctrl-H).
3. In the 'Find' box, enter this:

">"&EOMONTH(I_CHOSEN_MONTH,-1)

4. In the 'Replace' box, enter this:

">="&EOMONTH(I_CHOSEN_MONTH,-1)+23

5. Click 'Replace All'.
6. Open F&R again and enter this in the 'Find' box:

"<="&I_CHOSEN_MONTH

7. Enter this in the 'Replace' box:

"<"&EOMONTH(I_CHOSEN_MONTH,0)+22

8. Click 'Replace All'.

Note that I've changed > to >= and changed <= to <. This is because you can then see the start/end dates of your pay periods directly in the formulae (23 and 22), which I think is easier to understand.

That should do it. Hope that makes sense and does what you need.