Format

[Barry]

Excel 2003
Dear all
I have a spreadsheet listing some items with dates and these Items need
to be updated every 5 years, but I need to know when they are due 60
days beforehand.

I was given the following format, but this seems to take off 60 days
(off the present date).
The Date in say B4 shows the day the item was "issued" in C4 it should
tell me 60 days before it is due to be renewed. i.e. 5ive years minus 60
days.

The formula I was given looks like this:

=IF(B4-TODAY()-60,"renewal due","")

B4 being the issue date
TODAY = present date
etc

AS I have not been taught Excel, and therefore Formula, Can anyone help
please?
Thank you
Barry

 

[AdamV]

Well, since you want to compare
B4
plus 5 years
-60 days
to see if it is earlier than Today()

I would suggest:
IF(B4+(365.25*5)-60 < TODAY(), "renewal due", "")

To simplify, just work the maths through and round off the odd decimal:
IF(B4 + 1766 < TODAY(), "renewal due", "")

Note that TODAY() is calculated every time you make any change to any
cell in the entire workbook (this behaviour is known as a 'volatile'
function). If at all possible I would suggest you avoid this - you could
easily just refer to a specific cell, and put in there a 'compare
against' date (which you would then update to the current date, eg by
pressing CTRL-; in that cell). This also means you could use it more
flexibly, such as looking for all contracts that will be due for renewal
within 60 days by the time you get to the end of the month, which might
be useful when holiday periods are coming up and you may want to get a
little bit ahead of your normal cycle.

Hope this helps
Adam

 

[Bob Phillips]

Try

=IF(AND(DATE(YEAR(B4)+5,MONTH(B4),DAY(B4))>TODAY(),DATE(YEAR(B4)+5,MONTH(B4),DAY(B4))<=TODAY()+60),"due","")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Barry]

Dear AdamV
Your format appears to work, thanks.

I shall work through the spreadsheet and see.

It will hopefully help me to see when things need to be renewed. At
present, I get the calculator out and could miss someone!

Regards
Barry

 

[Barry]

Hi Bob
Many thanks for replying, however, I tried what you suggested but did
not seem to work.

I tried AdamV suggestion and appears to work ok (see reply to AdamV
Again, thank you for taking the time to answer
Barry

 

[AdamV]

Bob's looks fine to me, and is slightly more precise than mine in that
it looks at the actual calendar date in 5 years' time rather than just 5
* 365 days, but my assumption was that one day out here or there
(depending on leap years) would not really matter.

Bob's solution also only shows contracts due in the next 60 days, not
those which are already past due, whereas mine would show any which will
pass their renewal date at any point in the past or up to 60 days into
the future.
Depending on your business rules, overdue might mean "emergency -
overdue! Chase this one as well" or could mean "too late, don't bother".
As always, you need to choose a solution to show those you are looking
for and not bog down the view with things you are not really interested in.

 

[Bob Phillips]

I thought about that too AdamV. I decided that as the OP hadn't asked for
it, by having this due check, their processes would kick into stop it going
overdue. I am an optimist <g>

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)