Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a custom Financial Year Column

Hi,    I need to create a new column to record a financial year which runs from 1st September to 31st August. What I would like to have in as the out put would be, as an example, FY21/22.   Any i...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Start date of the financial year = 
IF(MONTH([Date])<9,DATE(Year([Date])-1,9,1),DATE(Year([Date]),9,1))
    financial year = YEAR([Start date of the financial year])
    FY = 
var curryear = right([financial year],2)
var newyear = right([financial year]+1,2)
return
curryear&"-"&newyear

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, Anonymous 

 

You can try the following methods.

Start date of the financial year = 
IF(MONTH([Date])<9,DATE(Year([Date])-1,9,1),DATE(Year([Date]),9,1))
financial year = YEAR([Start date of the financial year])
FY = 
var curryear = right([financial year],2)
var newyear = right([financial year]+1,2)
return
curryear&"-"&newyear

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thank you for these formulas. I needed to calculate academic year, which is similar, but starts on July 1. This is the formula I came up with (with your help). Is it right?

 

Academic Year =
var spring = IF(MONTH([Grad Date])<7,Year([Grad Date]),YEAR([Grad Date])+1)
var fall = IF(MONTH([Grad Date])<7,YEAR([Grad Date])-1,YEAR([Grad Date]))
return fall&"-"&spring
 
It seems like the same thing, but I don't need to calculate the first day of the year to get to this. Skip the middleman!