Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=505676 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date of expiry - warning?
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=505676 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Another Date issue. | Excel Worksheet Functions | |||
Cond Format: Expiry Date Alert | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |