Date of expiry - warning?

[Jonas]

Hi,
could anyone help in how to make a function that warns when a cell contains
a date that has expired due to a predefined timespan?

Basically what I have is a column with dates and I want to create a function
in a second column that warn whenever one of the dates are more then 3 years
old in relation to the current date.

Any suggestions?

All the best
Jonas

[broro183]

Hi Jonas,

try entering this in B4,

=if(DATEDIF(A4,TODAY(),"y")>3,"EXPIRED","okay")

where your existing date is in cell A4, or this could be adapted for use in conditional formatting if a visual warning is all that is needed.
See Chip Pearson's site, http://www.cpearson.com/excel/datedif.htm, for details of how to use this function.

Hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Jonas]

Thanks Robit worked perfect as you suggested
..
However, I have to change all , to ; to make it function. Why is that?
Is there a preference in excel that needs to be changed and could this
potentially lead to problems if the sheet is opened on a other computer with
a different preference settings?

Also, at the end of my data column is a number of empty cells, where the
text expiry will appear. Is it possible to modify the function to leave out
empty cells, i.e. leave them blank until filled with something?

All the best
Jonas

[Jonas]

Also,
when opening the sheet in a computer with excel installed with a different
language, will it still be able to perform the functions or do I have to
translate the function first? In my case I have a english version but
collegues work with swedish versions.

All the best

Jonas

[Jonas]

Hi,
Solved the blank cell problem by using the ISBLANK function and if true
returning a blank cell.

However the translation issue still remains as well as the comma and
semicolon issue.

All the best
Jonas

[broro183]

Hi Jonas,
Your question about translation is beyond me as I only work on a single home or a single work computer but someone else maybe able to help out.

re the comma versus the semi colon:
You may need two versions of the file (I'm not sure though) - have a quick glance at,
http://www.excelforum.com/showthread.php?t=503235
where Bob Phillips stated:

"Because we are using English language setting, where comma is not used as a decimal separator in numbers, so it can be used as the function separator.
As Continental settings us a comma in numbers, a different character is used
as a function separator. If you noticed in my Danish version I included a ;."


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...