Problem with concatenate function in google sheets

1
votes

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
google-sheetsgoogle-sheets-formula
What is the result of CONCATENATE(B9,$C$3))+1)? Looks like that needs to be a number, but it is returning a string according to the error you provided. – BMW
B9 is January and C3 is 2020, so concatenate of it should be January 2020 I think – Japnit Sethi
I'd suggest breaking your statement apart to make sure each function returns what you expect, if B9=january and c3=1, then concatenate(b9,c3) = january2020, and day(concatenate(b9,c3)) = 1. Try double checking this is what you get. – BMW
Sorry was out for a while! I checked day(concatenate(b9,c3)), and it is giving me back 1 – Japnit Sethi

1 Answers

1
votes

You may have had a temporary condition where C3 was blank. It is easy to run several tests to see what Day does with "Strings".

=Day("January" & 2020) returns 1, the day value of the first day of January.

=Day("January2020") also returns 1, even though "January2020" is clearly a string. =Day("Nothing2020") returns the error you saw, where DAY expects a numeric value.

If the string is one that Google Sheets recognises as a date type, it treats it as a number value for those functions requiring that.

"January2020" is seen as a valid date, but "January" is not.

Your error indicates to me that C3 was blank at the time of the error, since it said { "January" is a text, and cannot.....} . Let us know if you still have the error situation. If so, please share a copy of your sheet.