Assuming the date is selected in cell A1, =DATE(2010, MATCH($A

Assuming the date is selected in cell A1, =DATE(2010, MATCH($A$1,lst Months,0) COLUMNS($A$2: A2),1) in-case you got some other types of values instead of months: To make it a bit simple, I will use a helper cell where we can identify the position of selected month in the list of months, like this: I have assumed that Jan is 0, Feb is 1 … To get this type of output we must use an excel formula called as MOD. MOD formula takes 2 numbers tells us the remainder when first number is divided by second number. I ran your experiment and I don't get the same results as you do.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.

||

Assuming the date is selected in cell A1, =DATE(2010, MATCH($A$1,lst Months,0) COLUMNS($A$2: A2),1) in-case you got some other types of values instead of months: To make it a bit simple, I will use a helper cell where we can identify the position of selected month in the list of months, like this: I have assumed that Jan is 0, Feb is 1 … To get this type of output we must use an excel formula called as MOD.

MOD formula takes 2 numbers tells us the remainder when first number is divided by second number.

,lst Months,0) COLUMNS($A: A2),1) in-case you got some other types of values instead of months: To make it a bit simple, I will use a helper cell where we can identify the position of selected month in the list of months, like this: I have assumed that Jan is 0, Feb is 1 … To get this type of output we must use an excel formula called as MOD.

MOD formula takes 2 numbers tells us the remainder when first number is divided by second number.

I've since set up a small experiment which shows that these functions don't work the way I expect them to.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.Syntax of DATE Formula Example of DATE Formula Possible Errors returned by the DATE Formula DATE formula in Excel returns the number representing the date for a given day, month and year.The DATE formula is typically used to construct a date by providing the constituting parts such as day, month and year.