Adding Dates and Highlightin this date if overdue

[NimP]

Hi,


I am attempting to create a spreadsheet which allow susers to input a registration data and this will automatically update a 3 month, 6 month and 12 month date where users know they should update the clients records.

Once a 3 Month, 6 month, and 12 month date is due the cell will highlight to red unless an entry is added in the next cell.


Any help on this would be greatly appreciated please.

 

[DaveTurton]

You should be able to accomplished this with conditional formatting. Would you be able to upload some sample data?

 

[Hui]

NimP


Firstly, Welcome to the Chandoo.org Forums


This can be done using Conditional Formatting

Start Here: http://chandoo.org/wp/excel-tutorial/using-conditional-formatting/


Can you possibly upload a sample file so we can give you a more specific answer:

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[oldchippy]

Hi NimP,


Welcome to the forum, presumably the user enters a date of the entry into a cell, i.e. A2

if that is the case then you could use the EDATE formula, say in F2


=EDATE(A2,3) for 3 months

=EDATE(A2,6) for 6 months

=EDATE(A2,12) for 12 months


Then with the CF the formula to use is for F2 cell


=F2<TODAY() and pick red fill to format

 

[oldchippy]

@Hui..


The first link throws an error


EDIT: If you click the YouTube icon you can watch it there

 

[sgmpatnaik]

@oldchippy


HI


the link which is provided by Mr. Hui that is working perfect for my system and i am using Mozilla Firefox


i think there is some problem in your browser please upload the video Addin's in your Mozilla Firefox


Thanks


SP

 

[NimP]

Hi All,


Than you for your posts, I have managed to get it working now & am stuck on the last part. Below is an example of the 3 months data;


User enters Date in C2, this automatically updates Cells I2 (3 months)

Cell J2 shows if cell I2 is overdue by displaying "UPDATE"

Cell K2 requires user to select an option from a dropdown list.


I was hoping the following could be achieved,

Cell K2 will highlight red if no option from dopdown is selected and J2 displays "UPDATE".


Thanks for all your help thus far

 

[Hui]

@Oldchippy


Time to upgrade something at your end my friend


The link works fine for me in Firefox, Chrome & Internet Explorer

 

[DaveTurton]

Conditional formatting in K2

=AND($K2=0,$J2="Update")

and drag down

 

[oldchippy]

@Hui...


I'm using Chrome on the desktop with WinXP this end and must admit it seems to be playing up lately, not sure why. I only use them I don't look under the bonnet :-(

 

[oldchippy]

@Hui... and sgmpatnaik,


Just an update, I opened the bonnet, reloaded Chrome, still same problem, tried IE even worse, then changed to Firefox and working fine. Sticking with Firefox now :-0


Thanks for your input guys