finding a formula to help with dates.

R

Redsphynx

Hi I am trying to work out a formula to work out the following problem.

If "cell 1" is a later date then "cell 2" then "cell 3" is to be "older"
If "cell 2" is a later date then "cell 1" then "cell 3 is "the number of
days between them"
If "cell 2" has no date then "cell 3" remains blank.

If anyone can help me work this one out, I would greatly appreciate it.

Thanks.
 
R

Rick Rothstein \(MVP - VB\)

Using C1 for "cell 1" and C2 for "cell 2", put this in "cell 3"...

=IF(C2="","",IF(C1>C2,"Other",C2-C1))

If C1 and C2 are incorrect, simply change them to your cell 1 and 2
references.

Rick
 
T

Tyro

Dates in Excel are numbers. Jan. 1, 1900 is day 1, Dec 31, 9999 is day
2,958,465. The cells need to be formatted as dates for you so see a date. If
you format the cells as numbers, you will see a number.
To answer your question if date 1 is in cell A1 and date 2 is in cell A2
then in cell A3
=IF(A2="","",IF(A1>A2,"older",IF(A2>A1,A2-A1,"?")))
You did not state what to return if the dates are equal, so I returned a ?
The formula presumes there are numbers in A1 and A2 or an empty cell in A2

Tyro
 
J

Jean-Guy

Hi,

Try something like this:

=IF(OR(B1="",A1=B1),"",IF(A1>B1,"Older",B1-A1))

if the 2 dates are equal or date 2 is blank then the formula will return a
blank!

Hope this helps!
Jean-Guy
 
R

Redsphynx

Ok, this has worked perfectly. I have slightly modified it to:

If "E4" is a later date then "G4" then "cell 3" is to be "OLDER"
If "G4" is a later date then "E4" then "cell 3 is "the number of days
between them"
If "G4" OR "E4" has no date then "cell 3" remains blank.

=IF(E4="","",IF(G4="","",IF(E4>G4,"Other",G4-E4)))

What I want to do now is...

If "G4" is blank then I need to calculate how many days has elapsed till
today (or what ever date we open file in the future"

I hope this makes sense.

Thanks
 
R

Rick Rothstein \(MVP - VB\)

How many days has elapse from when? Assuming you mean from E4...

=IF(E4="","",IF(G4="",TODAY()-E4,IF(E4>G4,"Other",G4-E4)))

Rick
 
R

Redsphynx

Thanks once again Rick. Fantastic job !!!!!

Rick Rothstein (MVP - VB) said:
How many days has elapse from when? Assuming you mean from E4...

=IF(E4="","",IF(G4="",TODAY()-E4,IF(E4>G4,"Other",G4-E4)))

Rick
Click to expand...
 
Top