Time passed sinec date in column A

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am trying to greate a document that will tell me when someone needs
to renew thier training. I have 1, 2, 3, & 4 yearly training programs.
I want to have a system where I can set conditional formating to goy
amber when it has been 10 months since the date and then Red when over
a year>


I have a date on column A. In column B I want to know how many months
have passed since that date.

I am sure the solution is easy, but I can't work it out!!

Many thanks

Steve
 
Hi Steve,


Not a full answer to your question but the following might help to put you
on the right track.

To find out how many months have passed you can use a formula like this:-

Assume the date in A2 is April 10 2007 and today is Mar 10 2008
=MONTH(TODAY()-A2)

Above returns 11 However, depending on the actual day of the month in the
start date and end date, it could return 12 if actually more than 11 months.
Also months does not return greater than 12. (It rolls around to 1)

=EDATE(TODAY(),10) will return a date 10 months ahead of today. You can use
any valid date in place of today(). Negative number of months can be used
also. However, you need the add-in "analysis tool pak" loaded to use EDATE.

I have not been able to get EDATE to work in conditional formatting but if
you create a column with the data then you should be able to use the actual
data for Conditional formatting.
 
Steve, use the undocumented, (apart for XL2000), DATEDIF(). With the
Previous training date in A1, in B1 enter:

=DATEDIF(A1,TODAY(),"m")

Note: The number of months will change in the middle of the month is the
previous training date was in the middle of the month. Post backi f you
want the Training date to be the 1st of the month or the nearest start of
month.

In Conditional Formatting select *Formula is:* then enter:

=B1>=12 and select Red as the Pattern colour

In Conditional Formatting, Condition 2 enter:

=B1>=10 and select Amber as the colour


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top