Financial Year Saving Based on Implementation Date

Dan_Foster_1504

Hi All,

I have a sheet to track cost savings and would like the financial year savings to change based on the implementation date.

The three fields are:

Implementation Date - restricted date field.

Savings Full Year - the overall savings throughout the full financial year, 01 July 2020 to 30 June 2021.

Savings Financial Year - I'd like this field to calculate the cost saving that we'd benefit from in the financial year. E.g. if we had a 20,000 full year saving with an implementation date of January 2021, the financial year saving would be 10,000, whereas if the implementation date was 01 July 2020 the financial year benefit would be the full 20,000.

Any help would be greatly appreciated.

Dan_Foster_1504

I did manage to resolve this by changing the date field to a drop down list including "Jul-20", "Aug-20" etc. and added an additional column with a nested IF statement e.g. =if(XXXX = "Jul-20", 12, if(XXXX = "Aug-20", 11)) etc.

Then in the financial year column the formula is =sum([Full Year] / 12) * [Nested If Statement Formula]

Just in case anyone within the community has a similar issue in the future.