1

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.

Improve this question
2
  • 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?
    – Mike Smith - MCT
    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.
    – SPnovice2018
    Jul 2, 2018 at 15:06

1 Answer 1

Reset to default
1

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)
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.