Weeknum with text before

[UndwaterExcelWeaver]

I am trying to do a simple formula to add the word "Week" before the weeknum function but I am getting a #Value error for some reason. I am following the instructions exactly that I read on this so I'm not sure what the issue is. I achieved my goal visually by changing the number format to include the word "week" but that does not function correctly when matching that cell to that in another workbook, presumedly because it is really only seeing the week number and not the text that I added. Instead, here is the formula I'm using, where cell D1 contains the date:

=CONCATENATE("Week ",WEEKNUM(D1)+1)

The label "week ##" is in another sheet and I'm using that text as a reference point to pull in the correct data into this sheet, so I need the two sheets to have identical text while also updating automatically based on the date.

Thanks

EDIT: I have also tried the simpler ="Week " &weeknum(D1)+1. This does not work either.

 

[Cubist]

What is in D1?

 

[Joe4]

That seems to suggest to me that the value in D1 is not a validly entered date, but text instead.

 

[UndwaterExcelWeaver]

That seems to suggest to me that the value in D1 is not a validly entered date, but text instead.

The date I actually need is in F1 I guess, but the problem remains. All are formatted correctly as dates.

 

[Joe4]

Just because the cell is formatted as date does not mean that the entry is a valid date entry. Dates entered as text won't work properly. And changing the format of the cell does not change the underlying value.

Enter this formula in any blank cell and tell us what it returns:

=ISNUMBER(F1)

 

[awoohaw]

It is hard to tell if F11 is really a number formatted as a date.
try:

Book1
	E	F	G
10
11		2024-07-12	Week 28
12		2024-07-12	F12 Is Not A Number
Sheet2

Cell Formulas
Range	Formula
G11:G12	G11	=IF(ISNUMBER(F11),"Week " & WEEKNUM(F11),ADDRESS(ROW(F11),COLUMN(F11),4)&" Is Not A Number")
F12	F12	="2024-07-12"

 

[UndwaterExcelWeaver]

Just because the cell is formatted as date does not mean that the entry is a valid date entry. Dates entered as text won't work properly. And changing the format of the cell does not change the underlying value.

Enter this formula in any blank cell and tell us what it returns:

=ISNUMBER(F1)

1. If it was a date issue, wouldn't the Weeknum function not work at all? It works fine until I try to add "week" before it.

 

[felixstraube]

Can you share your data with XL2BB? Or upload the file and share the link?

 

[Joe4]

1. If it was a date issue, wouldn't the Weeknum function not work at all? It works fine until I try to add "week" before it.

1?

That doesn't make much sense. It should return TRUE or FALSE.
If you have any special formatting being applied to the cell you placed that formula in, can you set it back to "General", and tell me whether it returns TRUE or FALSE?

 

[UndwaterExcelWeaver]

1?

That doesn't make much sense. It should return TRUE or FALSE.
If you have any special formatting being applied to the cell you placed that formula in, can you set it back to "General", and tell me whether it returns TRUE or FALSE?