# How to create a calculated column formula for CPE year

I've been trying to figure out how to create a formula that will show what CPE year a user's training is in. Similar to fiscal year, which I did try FY formulas that are out there to no avail, a CPE year runs from 3/31/xxxx to 4/1/xxxx. So, 3/31/2018 to 4/1/2019 would be considered CPE period 2018.

I've tried the following formulas ("Attended" is the date field I am working with):

=IF(MONTH(Attended)>4," "&RIGHT(YEAR(Attended)+1,2),""&TEXT(Attended,"yy"))+0

=IF(DATE(YEAR(Attended),3,31)>Attended,YEAR(Attended),YEAR(Attended)+1)

The problem with either of those is that the CPE period is not renewing after the 3/31 date. So I have CPE period 2019 showing for an attended date of 5/23/2018 when it should be still CPE period 2018.

If anyone has any insight into what i am doing wrong or guidance please let me know.

• So in your example 4/1/2018 and 4/1/2019 are both in the same year? I.e. 2018? What happens with 4/1/2019 and 4/1/2020? Jun 30, 2018 at 3:11
• Yes, 4/1/2018 to 3/31/2019 is considered 2018 and then 4/1/2019 to 3/31/2020 is 2019 and so on. I just realized the example in my original post was incorrect. Jul 2, 2018 at 15:06

Assuming a single split date... i.e. 4/1/2018 is only in a single CPE year, then:

``````=IF(Attended<=DATE(YEAR(Attended),4,1),YEAR(Attended)-1,YEAR(Attended))
``````

or mathematically simplified:

``````=YEAR(Attended)-IF(Attended<=DATE(YEAR(Attended),4,1),1,0)
``````

If the split date is 3/31 then:

``````=YEAR(Attended)-IF(Attended<=DATE(YEAR(Attended),3,31),1,0)
``````