Strange Errors in calculating Week-No

[dvdung]

Hi all

I'm using the formula " ="W."& WEEKNUM(M20,1)" to calculate number of week and return the result W. Weekno.

However, i have to double click to this cell and press enter to get the result, or else the cell would display #Name ?

Can you help to give me the solution

Thanx much

[Mallycat]

Do you have calculation set to automatic? Tools\Options\Calculation\Automatic. Try pressing f9 rather than editing the cell, as this is a manual recalc.

Matt

[dvdung]

Even i've tried that, but still display #NAME, the error message is "Occurs when Microsoft Excel doesn't recognize text in a formula."

="W."& WEEKNUM(I27,1) => #NAME. You have to double click the cell, then it displays => W.WNo

Could you please to help all ?

Many thanks
Dung

[Bryan Hessey]

Even i've tried that, but still display #NAME, the error message is "Occurs when Microsoft Excel doesn't recognize text in a formula."

="W."& WEEKNUM(I27,1) => #NAME. You have to double click the cell, then it displays => W.WNo

Could you please to help all ?

Many thanks
Dung

Heve you tried removing the space from the formula? (it shouldn't make a difference . . . but . . )

[dvdung]

Let's me try, however the error does not come from generated excel file but from the program that extracts .

[Sprint`6]

The format is text in M21 the formula will not work unless it is an actual date. Test the cell and see if it is actually a date. I think you are importing this data from some other worksheet or program other than excel, the data may look like a date but in reality only be text. By changing the format to another date format mmm/dd/yyyy to m/d/yy for cell M21, if it does not change the way it is displayed it is not a (date) format, and when you manually enter the date you are then deleting the text data replacing it with a actual date. Temporarily widen the column it is in, it is aligns to the left it is not a date value, date values will align to the right of the column edge. Or another test you can do, in another open cell enter the formula " =ISNUMBER(M20)" it should return a 'TRUE" if it is a date and "FALSE" if it is something other. When importing a simple thing as a space can cause an error in the formula.