Recreating a financial year with dates, works in excel but not in Google sheets

Question

So I am trying to recreate something like this picture:

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

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.

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