Thanks for the suggestions I checked and 1904 date system was unchecked, I
made sure that I am not viewing the formulas, I even added another column and
did a =A1 to see what I would come back with and the correct date ex:
3/31/3010 was displayed.

Yet still in the formula tool I am seeing the numeric form ex. 39538.

I may have missed a key point here, in the A column I am pulling the data
from a DB sheet by using this formula

=IF(ISERROR(INDEX(ALL,SMALL(IF(customername=$B3,RO W(customername)),ROW(RawData!1:1)),2)),"",(INDEX(A LL,SMALL(IF(customername=$B3,ROW(customername)),RO W(RawData!1:1)),2)))

I am noticing in the main DB sheet that the date properties are ex: 39538 so
when excel is pulling the data from the main DB its converting it.

Any other suggestions?




--
Neall


"Dave Peterson" wrote:

First, I'm confused.

39538 is either March 31, 2008 when using 1900 as excel's date system
or April 1, 2012 when using 1904 as the date system.

This can be toggled via:
tools|Options|Calculation tab|Check or uncheck 1904 date system
(in xl2003 menus)

But dates are just numbers to excel.

So if you have March 31, 2010 in A1 and
=A1
in another cell (say B1), then make sure B1 is formatted as a date.

If you see that 39538 in the cell, but the date in the formula bar, then you're
looking at formulas:

Tools|Options|view tab|uncheck formulas (xl2003 menus)
or use the shortcut in any version
ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard)

===========
Saved from a previous post:

Be aware that any existing date will now be off by 4 years and 1 day. And
copying dates between workbooks becomes a problem, too.

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

Neall wrote:

I have been racking my brain to figure out why my formula is not working but
now I see that when reviewing the results through the functional argument
tool I see that excel is reading the day (march 31 2010) as 39538.

On the spreadsheet it clearly shows me the correct date format but when
excel is reading the formula it is not.

Can someone help?

Thanks
--
Neall

--

Dave Peterson