I have a negative number when finding the difference between 2 dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am calculating the number of days between two dates, some of the date fields are "blank" and this produces a negative number....this is the formula =DAYS360(E8,G8) ...... this is the result.... -37449. It really messes up my chart when I have negative numbers. What can I do to fix this? I will have various fields that will be blank because of the query that I run, so I can't filter out the blank fields
 
Hi
and what is your expected results for a blank cell (in this case if E8
is blank). If you want to calculate from the beginning of the year in
this case you may try
=DAYS360(MAX(E8,DATE(2004,1,1)),G8)

If G8 can also be blank what should happen (end of year??)
=DAYS360(MAX(E8,DATE(2004,1,1)),MIN(G8,DATE(2004,12,31)))

--
Regards
Frank Kabel
Frankfurt, Germany
texas Gunny said:
I am calculating the number of days between two dates, some of the
Click to expand...
date fields are "blank" and this produces a negative number....this is
the formula =DAYS360(E8,G8) ...... this is the result.... -37449. It
really messes up my chart when I have negative numbers. What can I do
to fix this? I will have various fields that will be blank because
of the query that I run, so I can't filter out the blank fields.
 
=IF(N(E8)*N(G8),DAYS360(E8,G8),#N/A)

texas Gunny said:
I am calculating the number of days between two dates, some of the date
Click to expand...
fields are "blank" and this produces a negative number....this is the
formula =DAYS360(E8,G8) ...... this is the result.... -37449. It really
messes up my chart when I have negative numbers. What can I do to fix this?
I will have various fields that will be blank because of the query that I
run, so I can't filter out the blank fields.
 
Hi
A good formula to try is DATEDIF. For example (A1=01-01-03 an
B2=01-01-04). The formula is as follows DATEDIF(A1,B2,"D"). That wil
calculate the exact number of days. If you need an error handelin
then use IF(ISERROR(DATEDIF(A1,B2,"D"))=TRUE,0,DATEDIF(A1,B2,"D")).
Good luck with it.

Stephen

texas Gunnyú×±jÈ.ž|«zj/zØb²k'r‰ I am calculating the number of day
between two dates, some of the date fields are "blank" and thi
produces a negative number....this is the formula =DAYS360(E8,G8
...... this is the result.... -37449. It really messes up my chart whe
I have negativ
e numbers. What can I do to fix this? I will have various fields tha
will be blank because of the query that I run, so I can't filter ou
the blank fields
 
Back
Top