So I am trying to recreate something like this picture: Financial Year in excel

But I just found out that the cell B7 does not work, the cell B7 contains the formula:

=IF($A$3="Financial Year",((CONCATENATE(B9,$C$3))-DAY(CONCATENATE(B9,$C$3))+1),EDATE(Summary!$C$15,-11))

In the above A3 cell is the one where I choose Financial Year, B9 is month January, C3 is the cell with the calendar year 2020 and the Summary tab is the picture here:Summary Tab where cell C15 is the starting date with the formula: =EOMONTH(TODAY(),-1)+1

Error I am getting from Cell B7:

Function Day Parameter 1 expects number values. But "January" is a text and cannot be coerced to a number

1 Answer 1


To overcome a similar problem with CONCATENATE in a sheet I am using I included the following expression (in a longer string):

TEXT(DATE($A$2;$A$3;$A$4);" MMMM")

where A2,A3,A4 contained YY MM DD respectively.

enter image description here

You'll need to adjust this to suit your circumstances, of course.

Your Answer

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