compute days between dates

[Guest]

I have two columns (B & C) of dates (mm/dd/yy). The first colmn is the result
of converting a Julian Date (col A) to the calendar date, so there is a
formula hidden in cell B under the date. I am using the formula
=DATEDIF(B27,C27,"d") to determine the number of days between the two dates.
This does not work with the two colums in my worksheet that has the hidden
formula. The answer I get is #NAME? (this is the formula behind cell B
=DATE(INT(C3/1000),1,MOD(C3,1000)) ).
If I put a date in col b (not a result of a formula from Col A) and use this
formula(=DATEDIF(B27,C27,"d") ) it works. Example 2
I am guessing the hidden formula is messing up my work?
I am using Excel 2000

EX. 1
Cols
A b c D
5001 01/01/05 01/10/05 #NAME?

Ex 2
A
Blank 01/01/05 01/10/05 9

 

[Guest]

It shouldn't matter the least, a cell with a formula that produces a date or
a hardcoded date would be the same and testing using your fromulas I don't
get an error, my guess is that the source formula somehow gives an error and
an error will always be transferred . Btw there is no need using DATEDIF for
days, a simple =C27-B27 is enough, just format result as general or else you
probably get date format

Regards,

Peo Sjoblom

 

[Guest]

Thank you Peo,
I changed to your simpler format and now I get the number 36534, how do I
make that the difference of 9 days? I have formatted the cell to a general
number.
Cols
A b c D
5001 01/01/05 01/10/05 36534
Thanks for your help and patience!

 

[Guest]

Format as a Number with no decimal places.

Thank you Peo,
I changed to your simpler format and now I get the number 36534, how do I
make that the difference of 9 days? I have formatted the cell to a general
number.
Cols
A b c D
5001 01/01/05 01/10/05 36534
Thanks for your help and patience!

 

[Fred Smith]

Excel stores dates as the number of days since Jan 1, 1900. That's why you
can simply subtract one date from another to get the difference in days.

36534 is Jan 9, 2000. So you have a difference of 9 days and 100 years.
Excel must think that your first date is in the 20th century, and your
second date is in the 21st.

Format columns B and C to have 4 digit years, so you can see which century
Excel considers them to be in. I know where column B comes from (it's
converting the Julian date in column A), but where does C come from? My bet
is that, for some reason, Excel thinks the date in column C is 01/01/2005,
whereas the date in column B is 01/10/1905. If so, one simple workaround is
to change my Julian date conversion formula to
=date(int(a1/1000)+100,1,mod(a1,1000))

 

[Guest]

Thank YOU Fred!!!
This has been a tremendous help to me! You were exactly right and it
convertted from 20 to 21st century! Thank YOU!

 

[Fred Smith]

You're welcome. Thanks for the feedback.