Nested IF Functions

[Kelly]

All three cells listed in the formula below are dates. If there is data in
cell R9, that cell should be calculated as below. If 01/00/00 is listed in
R9, the formula should calculate R4 in the same manner. I have started with
the following formula, but obviously have not incorporated in R4. Can anyone
help?

=IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

 

[Tyro]

Your formula makes no sense to me. What is 01/00/00? The first of 00 of
year 00? You refer to "three cells" yet you show only two $R$9 and A73 in
your formula.
R4 is dependent on R9 and its 01/00/00. Please explain in simple English
what you're trying to accomplish. Your formula appears to be saying that if
the difference in days is greater than 500, 0 is the answer, and if not, if
the difference in days is zero or negative, then 0 is the answer, then if
not, if the difference in days is greater than 0, "SUM($R$9-A73)" which
could be simply expressed as $R$9-A73. There is nothing to SUM. Your
formula can be reduced to =IF(OR($R$9-A73>500,$R$9-A73<=0),0,$R$9-A73)

Tyro

 

[Kelly]

the 01/00/00 is a blank date (R9); it is linked to another another
spreadsheet and waiting for an actual date to be entered once a deal is
closed. The third cell is R4. R4 is a "Current Through" date and R9 is an
"Acceptance" Date. There are many times when the Acceptance Date (R9) is not
included when I am processing commissions so I need to use the Current
Through Date (R4) to run the calculation. In my formula below I am only able
to use the Acceptance Date. Hopefully that makes more sense.

 

[Tyro]

Is 01/00/00 an Excel date or text? Blank dates are usually just blank cells
which are interpreted as 0, Jan 0, 1900

Tyro

 

[Kelly]

The date is coming from "B" spreadsheet, which someone hand typed in as
01/00/00 and formatted as a date. When I look at the cell where the date is
(spreadsheet "A"), it is a link to the "B" spreadsheet and has been formatted
as a date.