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.

 

[felixstraube]

Can you share with XL2BB or upload the file and share the link? (Or a copy with only the errors?)

 

[awoohaw]

Have you tried the formulas in a new workbook? Just paste this in a new workbook and see what happens? If it works, then paste it into a new sheet in your current workbook. If that works then I think it is something with your data (or system environment) and not excel.

Book1
	A	B	C	D	E	F	G
1				2024-07-05		2024-07-12	Week 28
2
3						D1 WeekNum:	27
4						D1 WeekNum + 1	28
5						F1 WeekNum:	28
6						"word week"	week
7						word week & WeekNum D1	week 27
8						word week & WeekNum D1 + 1	week 28
9						word week & WeekNum F1	week 28
Sheet2

Cell Formulas
Range	Formula
G1	G1	=CONCATENATE("Week ",WEEKNUM(D1)+1)
F3	F3	="D1 WeekNum:"
G3	G3	=WEEKNUM(D1)
F4	F4	="D1 WeekNum + 1"
G4	G4	=WEEKNUM(D1)+1
F5	F5	="F1 WeekNum:"
G5	G5	=WEEKNUM(F1)
F7	F7	="word week & WeekNum D1"
G7:G9	G7	=$G$6&G3
F8	F8	="word week & WeekNum D1 + 1"
F9	F9	="word week & WeekNum F1"

 

[Fluff]

If you need to keep the "Transition formula evaluation" checked, then you could put

=weeknum(f1)+1

in G1 & in another cell

=CONCAT("week ",G1)

 

[Joe4]

I am curious what these other formulas that break look like.
Could you post a few?
Maybe it is a quick fix, if we know what we are dealing with.