Can anyone offer a suggestion what excel is doing
It's operating on the numerical representation of the date (a serial number starting from 1/1/1900, or 1/1/1904, depending on your settings).
E1 =CONCATENATE(B1; "/"; C1; "/"; D1)
I'll try this, but why only 421 out of 3000 records?
I am not sure (I don't even have Excel) - but I suspect the real question should be why it works (or seems to be working) for the other 2,579.
Come to think of it, if Excel doesn't recognize those entries as valid dates... well, you'll see that as soon as you try to apply those date functions to them.
Thanks "comment", that did the trick on the 421 records. It would appear that for some reason some data held in the Dob field is in date format and the rest intext format, yet the field is formatted as a date field. Another reason to move to a new database!!!!
Comment wrote: It's operating on the numerical representation of the date (a serial number starting from 1/1/1900, or 1/1/1904, depending on your settings).
Curiously I have annother range where half the records have a very similar problem, except they =Year(F1) always returns 1903 ?????
Based on your previous ocmment about the base year, how can this be changed and would this expalin my current problem ?
Thanks in advance
What's actually in the A1 cell? Any spaces or hidden characters?
JUst to let you know, my formula had 1152 instead of AA1152 ( cell reference) hence the year was a constant 1903. Once corrected the calc was perfect. Thanks for your input.