dates arent working properly

[bigdan]

I have a number of dates in col A.
If I copy the cell and paste value I get a number like 43566, which I'd expect.

But if I write a formula in col B such as =datevalue(A1) I get a #VALUE ! error. Can anyone advise why this might be happening and if I can fix it?

 

[Gerald Higgins]

Well, from Excel's built in help on the DATEVALUE function . . .
The DATEVALUE function has one argument, Date_text, and this must be "Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents . . . "
The numeric value 43566 is not text in this context.

Why are you using DATAVALUE in this context ?
Why not just use your calculated value, and format as a date ?

If there is a possibility that the formula that is producing this value may return a date in text format, how about something like
=if(istext([YOUR FORMULA RESULT],DATEVALUE([YOUR FORMULA RESULT]),[YOUR FORMULA RESULT])