Dates formula Excell 2003

[Quez]

Hi, sorry if this is the wrong forum but I tried in newcomers and got an 'Invalid forum message.
I am seeking help with using dates on a database that I am constructing.
The equipment requires an annual safety check and I need to know which items are due for test.
My questions are;
Column I is for entering the date of test, Column J is for the date retest is due (1 year ahead). What formula do I need to use to automaticaly enter a date in column J when a date is entered in column I ?
Also what do formula do I need to tell when the date in column J is overdue i.e before the current date and can I have the rows with overdue retest dates print out?
As the first 10 rows of the sheet are used for headings, etc. I need to be able to ignore cells 1 to 10 on whatever columns the formula works on. Thanks in anticipation.
p.s. I forgot to mention that I also have a column to mark items as 'no longer in use' It would be nice if whatever formula I need to calculate overdue dates could ignore items marked so when printing.
The reason for leaving them in the database is that the details can be changed and the id numbers allocated to new items when old ones are scrapped

[Domski]

Say you have a date in I1 this will return the date one year on: =IF(I1<>"",DATE(YEAR(I1)+1,MONTH(I1),DAY(I1)),"")

Dom

[Quez]

Say you have a date in I1 this will return the date one year on: =IF(I1<>"",DATE(YEAR(I1)+1,MONTH(I1),DAY(I1)),"")

Dom

Thanks for such a quick reply Dom. My dates in column I start at row 10 (1 to 9 are used for headings etc). Where do I need to put this formula to enter the date in column J (also starts at row 10). Does it automaticaly ignore anything that is not a date?

[Marcol]

As Dom says and then perhaps conditionally Format J:J on these lines
Formula is:=

Please Login or Register  to view this content.

EDIT
Late in posting try
In J10

Please Login or Register  to view this content.

This will ignore text and any number below 36526 that might be confused for a date (01/01/2000)

EDIT 2
You could also consider setting the data validation of the relevant cells in Column I
Data Validation > Settings > Allow:= Date ................

Hope this helps

[Domski]

Just change the ref: =IF(I10<>"",DATE(YEAR(I10)+1,MONTH(I10),DAY(I10)),"")

Dates are stored in Excel as numeric values so guess you could use ISNUMBER like this:

=IF(ISNUMBER(I10),DATE(YEAR(I10)+1,MONTH(I10),DAY(I10)),"")

It will work for any number entered though, not just dates.

Dom

[Marcol]

Behind the curve here, slow broadband again!

It will work for any number entered though, not just dates.

See amended Post #4

[Quez]

Please bear with me,I am really new to Excel. I used Doms formula =IF(I10<>"",DATE(YEAR(I10)+1,MONTH(I10),DAY(I10)),"") in column J, highlighting the column and using the conditional format box.
I then entered a date in column I and nothing happened in column J. What am I doing wrong?
Marcol's example worksheet looks great but I am not sure what row K indicates? Idealy I need to have any rows that have dates in J that are older than the current date print the complete row for each item.
Can I have two conditions in the same row? One to display the due date and one to calculate if that date is overdue and print out the whole row?
Please bear with my ignorance and if possible tell me step by step exactly what and where I need to enter Quez.

[Marcol]

Column K is just a flag to indicate if the task has been completed, it can be any column of your choice and refer to any value in that column.

See the conditional formatting in this workbook, it warns if a "test" is overdue or if one is due in the next 7 days.