hi there

i am currently trying to write a formula to give me a warning sign if i date entered is later than for instance 31/3/2013

i am working with alot of price figures and i would like to keep track of my pricing which cannot be older than 6 months, should a price i have entered be older than 6 months, i would it to give me a warning on this so as i know to get a new price.

i hope this makes sense

Hello Chantel,

You can use Data Validation to manage this requirement.
Assuming you are entering the value in cell A1, follow the below steps:
1. Select Data - Validation
2. Under Allow: - select Custom
3. Under formula: - enter this formula: =A2>Today()-180
here 180 represents 180 days, i.e. 6 months. System will not allow you a date which is older than 180 days.
You can also display error message and an input message from the data validation dialog box.

Please note that this is a dynamic formula, excel will take the current date as today. So if you open the sheet 1 month later (say 1st of December), you could have trouble entering old data because that will be validated against the 1st of december and not against today's date.

Another way to handle this requirement is to store the current date in a cell and then validate the data against that cell instead of validating it against today()

Hope this helps.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.