Problem with concatenate function in google sheets

Question

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

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
– BMW, Commented Nov 14, 2020 at 0:45
B9 is January and C3 is 2020, so concatenate of it should be January 2020 I think — Japnit Sethi
– Japnit Sethi, Commented Nov 14, 2020 at 0:54
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
– BMW, Commented Nov 14, 2020 at 1:11
Sorry was out for a while! I checked day(concatenate(b9,c3)), and it is giving me back 1 — Japnit Sethi
– Japnit Sethi, Commented Nov 14, 2020 at 2:00

kirkg13 · Accepted Answer · 2020-11-14 04:04:50Z

1

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.

answered Nov 14, 2020 at 4:04

kirkg13

3,0281 gold badge10 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with concatenate function in google sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related