Automatic updating date in excel
There are a number of date formats in use and with each department, company and country following their own nomenclature. Check out the clip above for knowing if the values you’ve entered are in the same order. If you wanted to enter 2009 but mistakenly entered 20009 in say =DATE(20009,4,7), that would result in this error.
I personally tend to like the dd-mmm-yyyy or the mmm-dd-yyyy formats so that there is no room left for ambiguity. In the end your formula should look something like this =DATE(2009,6,28) The DATE formula can result in the following error values: If the parameters specified in the year, month or day cause the resulting date to exceed the minimum and maximum limits (January 0, 1900 and December 31, 9999 in Excel 2003), the DATE function would result in the #NUM! If the parameters have been specified incorrectly (entering string instead of a number), the DATE function can result in the #NAME? For example, say you entered “jul” as the month instead of 7 (which is the number representing the month July) and entered =DATE(2009, jul, 30), that would result in the #NAME?
To set up such dynamic rolling months in Excel, just follow these simple steps: Enter the month names in a bunch of cells (Tip: Just enter the first month name and then click at the bottom right corner of that cell and drag to get all the other month names).
Let us call this range as and set up data validation list on it (so that users can only enter a valid month in that cell) and use “List” type as validation.
I changed the setting as indicated above; but the auto-recalculate still did not work, and upon rechecking the "Calculation" option, found it had reset itself back to "Manual" all by itself.
Three attempts later and it was still adamant it wanted to be "Manual" and nothing else.
My cell A1 stays a Date the whole time, and B1 stays 41575. Results probably depend on how your Excel version/settings choose to automatically detect/change a cell's number format based on its content.Question #2, "How can I ensure that a cell will return a date value": well, not sure what you mean by "return" a date value, but if you want it to @JFC You are absolutely right! In that case you still will be better off with assigning the error number and switch the error handling on.In this example it would not make much difference, but it would be different story with the more complex "if" statement.Ctrl F9 not only updated the values, but fixed the problem so it didn't happen again.
I had a case of this just now on Excel 2010: a particular spreadsheet that would not auto-recalculate.[Excel MOD formula, Introduction, Syntax & Examples] Very simple. I have prepared a short example workbook where this technique is demonstrated.