Why are my dates show up as (example) 39538 in formula?

[Neall]

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

 

[Jacob Skaria]

Hi Neall

The below is a link by Chip on excel date/times...Once you go through post
back incase you have any issues in working with formulas with date...

http://www.cpearson.com/excel/datetime.htm

If this post helps click Yes

 

[Mike H]

Hi,

Dates in Excel are numbers - format one as general to see that - and we only
see them as dates because of the applied formatting.

The problem your experiencing arises because in a formula that format can be
lost and has the be added back by formatting the formula cell as a date or
using the TEXT function.

If you post the problematic formula then someone will help you sort it out.

Mike

 

[Dave Peterson]

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]

Thanks everyone

So, I have a column of dates that are formatted and showing ex:2008 when
formatted by date however, if I look at the same date fields formatted as
"normal" I see 39538


I am trying to get a sum of all paid part numbers by year
(2007,2008,2009,2010) which is this

=SUMIF(A8:M23,K24,L8:L23)

A8: M23 is the entire table
K24 - 27 are the date variable (2007,2008,2009,2010)
L8 - L23 are the prices
A 8 - A 23 are the dates columns

Basically in this table, show me the sum of part numbers purchased for each
year.

Hope you can help

 

[JoeU2004]

A8: M23 is the entire table
K24 - 27 are the date variable (2007,2008,2009,2010)
L8 - L23 are the prices
A 8 - A 23 are the dates columns

Basically in this table, show me the sum of part numbers purchased
for each year.

I presume you mean you want the sum of the prices.

=sumproduct((year(A8:A23)=K24)*(L8:L23))


----- original message -----

 

[Neall]

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,ROW(customername)),ROW(RawData!1:1)),2)),"",(INDEX(ALL,SMALL(IF(customername=$B3,ROW(customername)),ROW(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?

 

[Dave Peterson]

Format the cell with the formula as a date.

Formulas don't bring back formatting. You'll have to do it yourself.

But your dates are still not going to be what you've shared.

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,ROW(customername)),ROW(RawData!1:1)),2)),"",(INDEX(ALL,SMALL(IF(customername=$B3,ROW(customername)),ROW(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]

Is there anyway I can send you screen captures of what I am seeing.

All date fields are showing the correct date format or what I need to get my
formula to work, but when I review the information through the function
Arguments window where I select the range I see the 39538 date format which
is not the format I have selected or see in the actual spreadsheet.