Date Formula that will return a result for a specific period?

[mattbird]

Hi,

I trying to create a formula from a date to return a training year result ie:

The training year runs from 1 Apr - 31 Mar.

If I add a date between 1 Apr 2023 - 31 Mar 2024 in Cell H2 (date attended), for example 01/07/2023, I want it to return 2023-24 in Cell I2 (training year).

If I add a date between 1 Apr 2024 - 31 Mar 2025, for example 08/12/2024, I want it to return 2024-25 and so on.


How do I do this?

All help is greatly appreciated

 

[pjmorris]

try this:

=IF(MONTH(H2)<4,YEAR(H2)-1&"-"& RIGHT(YEAR(H2),2), YEAR(H2) & "-" &  RIGHT(YEAR(H2)+1,2))

HTH

 

[Fluff]

How about

=IF(MONTH(H2)<4,YEAR(H2)-1&"-"&RIGHT(YEAR(H2),2),YEAR(H2)&"-"&RIGHT(YEAR(H2)+1,2))

 

[pjmorris]

snap

 

[mattbird]

try this:

=IF(MONTH(H2)<4,YEAR(H2)-1&"-"& RIGHT(YEAR(H2),2), YEAR(H2) & "-" &  RIGHT(YEAR(H2)+1,2))

HTH

Thanks that works perfectly

 

[pjmorris]

Thanks that works perfectly

Excellent. Thanks for letting me know.