Help with financial year dates

AntonyCole1979 · 11-16-2020 06:52 AM

Hi, I need help with setting the financial year dates for my company so I can use them in a filter later on. I have 1 field [Fin Year From] and another field [Fin Year To]. Our financial year is 1st April to 31st March every year. I want the [Fin Year From] field to display 1st April and the current year now, and the [Fin Year To] field to display 31st March with the following year, that will then give me the current financial year dates. Is this possible?

Thanks

Antony

LeventK

[Fin Year From]

DATE(CONCATENATE("4/1/",YEAR(TODAY()))

[Fin Year To]

DATE(CONCATENATE("3/31/",YEAR(TODAY())+1)

AntonyCole1979

Thanks Levent! thought it needed a concatenate in there

Antony

Suvrutt_Gurjar

Just in case you wish the expressions to work from 1st January to 31st March of the financial year, then I believe the compact , nice expression shared by @LeventK could be further expanded to something like

[Fin Year From]

IF(
TODAY()>=DATE(CONCATENATE(“4/1/”,YEAR(TODAY()))), DATE(CONCATENATE(“4/1/”,YEAR(TODAY()))),
DATE(CONCATENATE(“4/1/”,YEAR(TODAY())-1)))
)

[Fin Year To]

IF( TODAY()>=DATE(CONCATENATE(“4/1/”,YEAR(TODAY()))),
DATE(CONCATENATE(“3/31/”,YEAR(TODAY()+1))),
DATE(CONCATENATE(“3/31/”,YEAR(TODAY())))
)

Steve

My turn!

DATE("4/1/" & YEAR(TODAY()))

EOMONTH(DATE("4/1/" & YEAR(TODAY())), 11)

Suvrutt_Gurjar

Hi @Steve,

Very compact expressions you have given. May be I am missing some point. I will request your guidance on the following.

I believe the expressions need to give financial year of start date of 1st April 2020 to last data 31st March , if tested on any day during that financial year.

As per my understanding, the below expressions will give incorrect from and to dates, if tested from 1st January till 31st March of any financial year.

For example for 04/01/2020 to 03/31/2021 financial year.

DATE(“4/1/” & YEAR(TODAY())) will give “4/1/2020” as start date if tested with TODAY() from 04/01/2020 to 12/31/2020. However on 01/01/2021 and thereafter , I believe the expression will return “4/1/2021”

Similarly with the end date expression.

In short , when calendar year flips, since the financial year still continues till March 31st. So I had suggested the said extension to @LeventK’s expressions also.

Will appreciate your guidance. I am probably missing some point.

Steve

You are correct, good sir! I misunderstood the requirements.

Suvrutt_Gurjar

Hi Steve,

Thank you. Appreciate your guidance as always.

AntonyCole1979

Thanks everyone,

Anony

sunil_prajapati

& how to get a financial year in 2020-2021 formate in a single column from [date] column. the year will be start & end from 01/04/2020 to 31/03/2021

LeventK

@sunil_prajapati

sunil_prajapati

thanks a lot, sir

perfect I tried a lot for this.

just made one change in this for correction of the result.

IF(
AND(
[date]>=DATE(CONCATENATE(“4/1/”,YEAR(TODAY()))),
[date]<=DATE(CONCATENATE(“3/31/”,YEAR(TODAY())+1))
),
YEAR(TODAY())&"-"&YEAR(TODAY()+365),
“”
)