Calculating Number of overdue dates with certain conditions

  • Thread starter Thread starter joannanpa
  • Start date Start date
J

joannanpa

I am trying to calculate the number of calendar days overdue based upon
a scheduled date (H32) and an actual date (J32).

I need to have a function that will address 3 possible scenarios:

1. Where Actual date (J32) and scheduled date (H32) have occurred (if
calculation is negative, I need the result to be 0-I'm having problems
with this)

2. Where actual date (J32) has not occurred yet and scheduled date
(H32) has past, I need the net days calculated from today's date

3. Where scheduled date (H32) is not due yet (and actual is not
entered) I need the result to be zero.

This is what I have so far:

=IF(H32>TODAY(),0,IF(H32>J32,0,IF(J32=0,TODAY()-H32,J32-H32)))

Any help is greatly appreciated. Thanks in advance!
 
Joanna,

Trying to follow your conditions, this is what I get

=IF(AND(H32<TODAY(),J32<TODAY()),0,IF(AND(H32<TODAY(),TODAY()<J32),TODAY()-H
32,IF(H32>TODAY,0)))

but there are some other conditions not covered here.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Unfortunately, it's not working for me. I may not be explaining th
goals clearly enough so I'll explain specifically what I'm trying t
do:

House is supposed to be finished on a scheduled date (H32), let's sa
January 14. Most often, the home is finished on the same date (actua
date, J32), therefore the result would be zero (J32-H32). Rarely, th
home is completed before the scheduled date and in this case (wher
H32>J32), the result should be zero (not a negative # because we don'
give credit for finishing early) Last scenario: House isn't finished
therefore Actual date (J32) is blank. If the scheduled date has passed
I need to start counting the days. My original function works in all o
the cases except the last scenario. I get a result of zero because H3
is greater than J32 (zero).

I'm not sure about how to use the AND function properly although i
seems like a logical place to use it.

Any other thoughts? Thanks again
 
Joanna,

How about this

=IF(J32=0,MAX(TODAY()-H32,0),MAX(J32-H32,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you so much Bob! Works perfectly.

Now I have to do a crash course on the MAX function...

Joanna
 
Back
Top