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]

Try adding TRIM:

="Week " &weeknum(trim(D1))+1

 

[Fluff]

As long as the "date" can be recognised as such the weeknum function should work on it's own.
If it does, go to File, options, advanced, scroll down to the bottom & make sure the last two boxes are unchecked.

 

[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?

This works fine...

 

[UndwaterExcelWeaver]

Try adding TRIM:

="Week " &weeknum(trim(D1))+1

still returns #value

 

[Fluff]

See post#12

 

[UndwaterExcelWeaver]

As long as the "date" can be recognised as such the weeknum function should work on it's own.
If it does, go to File, options, advanced, scroll down to the bottom & make sure the last two boxes are unchecked.

What section are you referring to exactly? My version has Lotus integrated and that is at the bottom. Do you mean under General? uncheck "multi-threaded processing"?

 

[Fluff]

No I mean under advanced, you need to uncheck the "Transition formula evaluation"

 

[UndwaterExcelWeaver]

What section are you referring to exactly? My version has Lotus integrated and that is at the bottom. Do you mean under General? uncheck "multi-threaded processing"?

So I unchecked "Transition formula evaluation" and it made my formula work but it broke nearly half of my spreadsheet's formulas below it, so it appears that will not be an option. I traded 1 #value for about 100.

 

[Joe4]

So I assume that this workbook was not originally created in Excel, but something else like LOTUS Notes?
If so, it might be time to update it (or rewrite it) if you wish to add Excel functionality to it.

 

[UndwaterExcelWeaver]

So I assume that this workbook was not originally created in Excel, but something else like LOTUS Notes?
If so, it might be time to update it (or rewrite it) if you wish to add Excel functionality to it.

No, it was made in excel and this it is actually a pretty advanced workbook but this particular formula is just throwing fits and doesn't want to work. We don't even use Lotus. I think it just shows up in settings because it's also installed on our network.