Difference between two dates with if statements

[Elena]

I'm a beginner...Please help

Dates are inputted in column M or H; but sometime dates are not required in
M and H which would leave it blank (if date is in column M there will not be
a date in column H and vice versa);

Cell B1 should always prompt today's date (=today())

I need column N to calculate days between them
example: B1=05/08/08 M=05/21/08 N should = 13
But if there's nothing in M and H it returns -39576
In that case I need "0" to be inputted verses -39576
and when B1 is greater then M/H column
I need the return to be "Past due "number of days between the dates"

Hope this makes sense.

Please help

Elena

 

[akphidelt]

Alright, this is untested so just give it a shot and let me know how it goes...

=If(Len(M1)<1,0,If(Or(B1>M1),"Past Due "&B1-M1&" number of days between the
dates",M1-B1))

Let me know how that works

 

[Lars-Åke Aspelin]

I'm a beginner...Please help

Dates are inputted in column M or H; but sometime dates are not required in
M and H which would leave it blank (if date is in column M there will not be
a date in column H and vice versa);

Cell B1 should always prompt today's date (=today())

I need column N to calculate days between them
example: B1=05/08/08 M=05/21/08 N should = 13
But if there's nothing in M and H it returns -39576
In that case I need "0" to be inputted verses -39576
and when B1 is greater then M/H column
I need the return to be "Past due "number of days between the dates"

Hope this makes sense.

Please help

Elena

Try this:

In cell N1 put
=IF(H1="",IF(M1="",0,IF(M1<B$1,
"Past due "&B$1-M1,M1-B$1)),IF(H1<B$1,"Past due "&B$1-H1,H1-B$1))

This formula first looks in the H column to see if it is blank.
If it is blank the next check is to see if the M column is blank.
If that is also true then 0 is the result.
If M is not blank then a check is made to see if the date in the M
column is less than the date in B1 (i.e. TODAY).
If so the result is Past due... etc
Otherwise the result is just the difference between the date.
Finally, if the H column was not blank the same checks are done
and similar results as just described for the M column is returned.

Hope this helps. / Lars-Åke

 

[BoniM]

=IF(AND(H1-$B$1<0,M1-$B$1<0,H1<>M1),"Past due
"&-MAX(H1-$B$1,M1-$B$1),MAX(H1-$B$1,M1-$B$1,0))

Enter in N1 and copy down...
Hope this helps... or maybe that someone comes up with a shorter way...

 

[Lars-Åke Aspelin]

This formula gives 0 if the same date, less than today, is put in
both H and M. It is not clear from the original post if there could be
dates in both H and M so it is hard to tell if this is a problem or
not.

The formula can be shortened a bit; the first of the $ in $B$1 can be
removed in 6 places.

/ Lars-Åke

 

[BoniM]

from original:
(if date is in column M there will not be