Calculating days in a range that fall within a financial year

Platform discussions
, ,
1

Hi everyone,

I’m hoping someone can help me figure out the correct formula for this — I’ve tried multiple variations but keep getting wrong results.

I have two separate columns: {Start Date} and {End Date}. I am trying to write a formula to return the number of days between these two dates that fall between 1 April 2025 and 31 March 2026.

What I’ve tried so far:

IF(
AND(
{End Date} >= DATE(2025, 4, 1),
{Start Date} <= DATE(2026, 3, 31)
),
DAYS(
MIN({End Date}, DATE(2026, 3, 31)),
MAX({Start Date}, DATE(2025, 4, 1))
) + 1,
0
)

  • I understand that DAYS() excludes the start date, so I added + 1 to make it inclusive.
  • So far, no matter what tweaks I can try the result always seems to come back as 0.

My main question:
:white_check_mark: Does anyone have a working formula that accurately counts how many days within {Start Date} and {End Date} fall within a defined period?
:white_check_mark: Any tips for common pitfalls I might be missing with DAYS(), MIN(), or MAX() functions?

Any help or working examples would be really appreciated!
Thanks so much in advance :folded_hands:

2

There might be a simpler way to do it, but a workaround I thought of is to add another column.

Actual Days: Days (End Date - Start Date)+1
Formula column to capture the ‘out of range’ dates (this is the formula for the dates before 4/1/25). IF(ROUND(DAYS(DATE(2025,4,1), {Start Date}), 0) < 0, 0, ROUND(DAYS(DATE(2025,4,1), {Start Date}), 0))

You’ll need another formula for the dates AFTER your cut off date. Then a formula column to the 'Actual Dates minus the ‘out of range’ dates.

I know it’s messy, can likely be done differently but that’s what I was able to come up with quickly.

Desiree - www.thecleverclovers.com

3

Hi @georgina-pe,

  1. Don’t use the DATE function in your comparisons, DATE(2025,4,1) returns “Tue Apr 01 2025 00:00:00…” which is not the same format as 2025-04-01 (or whatever date is in your {Start Date} column).

  2. Try this formula instead:

    IF({End Date} < "2025-04-01", 0, IF({Start Date} > "2026-03-31", 0, 
    IF({Start Date} < "2025-04-01", 
          IF({End Date} > "2026-03-31", DAYS("2026-03-31", "2025-04-01")+1, 
               DAYS({End Date}, "2025-04-01")+1), 
         IF({End Date} > "2026-03-31", DAYS("2026-03-31", {Start Date})+1, 
              DAYS({End Date}, {Start Date})+1)
)))

  3. Should you need to write the result in a Numbers column or count only the number of work days, you would need our Advanced Formula Booster app. Here would be the syntax of the formula:

2025-07-09_10-39-26
2025-07-09_10-39-26619×291 90.9 KB

{DaysInPeriod} is the Numbers column you would create instead of the Formula column.

  • Lines 2 and 3 allow you to easily change the fiscal year dates when needed.
  • To calculate the number of work days and therefore exclude week-ends and company holidays, simply replace COUNTDAYS BY COUNTWORKINGDAYS in Line 10.

 

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

4

Hi @georgina-pe - if you’re open to using apps, this is something the Smart Timeline + Duration app can help simplify.

You can use this automation:

When both the start and end dates are set,
automatically calculate and display the duration.

This gives you the exact number of days between your Start Date and End Date, stored in a real Duration column, no formulas needed, and no issues with inclusivity or formatting.

From there, you can easily filter or report on durations that fall within a specific window, like your fiscal year (April 1, 2025 – March 31, 2026).

There are over 20 automation recipes available in the app, so you can tailor the logic even further based on your workflow.

Let me know if you’d like help setting it up!

5

Thank you so much!

1 Like
6

Ah thank you, that works - really appreciated!