Convert historical date to same date in FY 2022

[estephenkim]

I'm trying to convert a historical date to the same date in U.S. Government fiscal year 2022 and could use some help with the formula. For illustration, I've provided below a sample historical date which I want to convert to the same date in FY22.

• Sample historical date: 07/23/01
• Fiscal year 2022 date: 07/23/22

Formula =DATE(YEAR(IF(MONTH([Historical date])>=10,2021,2022)),MONTH([Historical date]),DAY([Historical date]))

I've tried the above formula, but I'm noticing that Excel is reading my IF THEN ELSE formula options of 2021 and 2022 as serial numbers, and thus returning 07/23/05. I'm drawing a blank as to how to properly construct the formula and would appreciate some suggestions. I'm also open to using a different formula, so if you have a clean, efficient way to structure this, then I would welcome your ideas.

Thanks,

Stephen

 

[Fluff]

How about

=DATE(IF(MONTH([@[Historical date]])>=10,2021,2022),MONTH([@[Historical date]]),DAY([@[Historical date]]))

 

[estephenkim]

That worked - nice!!! It looks like I didn't need the YEAR function after all. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.

I would also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[estephenkim]

Done - thanks!