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.

"Tyro" wrote:

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

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-A73500,$R$9-A73<=0),0,$R$9-A73)

Tyro

"Kelly" wrote in message
...
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-A73500,0,IF($R$9-A73<=0,0,IF($R$9-A730,SUM($R$9-A73))))