Formula to determine if date is in current fiscal year (fiscal year beginning in Aug)

[seaottr]

Hi all,

I found formulas to calculate the fiscal year, but I'm trying to figure out a dynamic formula that will calculate if a date is within the current fiscal year (fiscal year for me starts in August).

For example, I would like the result to show the following for these dates (Today=12-22-2022):

03-15-2022 = No
07-31-2022 = No
08-01-2022 = Yes
01-02-2023 = Yes
06-17-2023 = Yes
08-04-2023 = No

I don't want to have to manually change the formula at the start of the next fiscal year (so I don't want the formula to be something like if date is between Aug 1, 2022 and July 31, 2023...and I don't want to use a lookup formula, as that still requires manual updates).

Any help would be greatly appreciated.

Thank you so much in advance!

 

[Fluff]

How about

=IF(MEDIAN(A2,DATE(YEAR(TODAY()),8,1),DATE(YEAR(TODAY())+1,7,31))=A2,"Yes","No")

 

[seaottr]

How about

=IF(MEDIAN(A2,DATE(YEAR(TODAY()),8,1),DATE(YEAR(TODAY())+1,7,31))=A2,"Yes","No")

Thank you SO much for the quick solution Fluff!!! This works perfectly! I've never used the Median formula before...I don't quite understand the formula, but it works great! LOL!

Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[seaottr]

How about

=IF(MEDIAN(A2,DATE(YEAR(TODAY()),8,1),DATE(YEAR(TODAY())+1,7,31))=A2,"Yes","No")

Hey Fluff...So, now that it's a new year, I actually found out that this formula doesn't actually work properly.

It's only showing "Yes" if the date is Aug 1, 2023 or later. The current fiscal year for me goes from Aug-Jul, so any date between Aug 1, 2022 and Jul 31, 2023 should show yes. And then when Aug 1, 2023 rolls around, any dates from then until July 31, 2024 should show "Yes".

Hopefully that makes sense?

 

[Fluff]

Ok, how about

=IF(MEDIAN(A2,DATE(YEAR(TODAY())-1,8,1),DATE(YEAR(TODAY()),7,31))=A2,"Yes","No")

 

[seaottr]

Ok, how about

=IF(MEDIAN(A2,DATE(YEAR(TODAY())-1,8,1),DATE(YEAR(TODAY()),7,31))=A2,"Yes","No")

No, that won't work unfortunately. When Aug 1, 2023 rolls around, it will show "Yes" for any dates between Aug 1, 2022 and Jul 31, 2023

I was able to come up with this formula, which isn't pretty, but works. Not sure if you have something simpler, but no worries if not:
=IF(MONTH(TODAY())>=8,IF(MEDIAN(A2,DATE(YEAR(TODAY()),8,1),DATE(YEAR(TODAY())+1,7,31))=A2,"Yes","No"),IF(MEDIAN(A2,DATE(YEAR(TODAY())-1,8,1),DATE(YEAR(TODAY()),7,31))=A2,"Yes","No"))

 

[Fluff]

Try

=IF(MEDIAN(A2,DATE(YEAR(TODAY())-(MONTH(TODAY())<8),8,1),DATE(YEAR(TODAY())+(MONTH(TODAY())>=8),7,31))=A2,"Yes","No")