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
--
Ron

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

"Ron" wrote:

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
--
Ron

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!
--
Ron


"Peo Sjoblom" wrote:

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

"Ron" wrote:

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
--
Ron

Format as a Number with no decimal places.


"Ron" wrote:

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!
--
Ron


"Peo Sjoblom" wrote:

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

"Ron" wrote:

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
--
Ron

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))

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Ron" wrote in message
...
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!
--
Ron


"Peo Sjoblom" wrote:

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

"Ron" wrote:

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
--
Ron

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!
--
Ron


"Fred Smith" wrote:

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))

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Ron" wrote in message
...
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!
--
Ron


"Peo Sjoblom" wrote:

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

"Ron" wrote:

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
--
Ron

You're welcome. Thanks for the feedback.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Ron" wrote in message
...
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!
--
Ron


"Fred Smith" wrote:

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))

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Ron" wrote in message
...
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!
--
Ron


"Peo Sjoblom" wrote:

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

"Ron" wrote:

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
--
Ron