0

I have this weird excel problem where excel won't calculate dates correctly. I had these dates in a column A:

10/12/2012
11/12/2012
12/12/2012
13/12/2012
14/12/2012
and so on.

In column B I wanted excel to calculate the next date. Everything was fine up to 12/12/2012. After that it shows #VALUE. I thought it was a date format problem and tried every available option, but it simply won't work.

2
  • 3
    Welcome to superuser. Details are important. Can you edit your post to include the formula you are using in B? – CharlieRB Apr 16 '13 at 12:01
  • Is it possible that the cell where the error shows isn't of the format date? – Random IT Guy Apr 16 '13 at 12:05
2

I find it a bit suspicious that your problem starts beyond "12". I would not be surprised at all, if your Excel interprets what you mean as a "day" as a "month". That might be depending on your locale or the assiciated settings in Windows.

US vs. UK

MDY vs. DMY

http://en.wikipedia.org/wiki/Date_format_by_country

0

Excel might be interpreting 13/12/2012 as a string because it expects dates to be entered in the US d/m/yyyy format (12/13/2012 for 2012-Dec-13). Try entering your dates using the three-letter abbreviation for the month instead. Then Excel knows what you mean and you can set the format how ever you want.

3
  • Excel recognizes and stores dates as serial numbers, not a format. The date can be formatted in various ways, but not the other way around. What dates mean to Excel. – CharlieRB Apr 16 '13 at 12:34
  • While true Excel recognizes and stores dates as serial numbers, as TheUser1024 points out in their answer, you can set your Excel (or Windows, I forget which needs to be changed) locale to use either US or UK date formatting. – Amer Apr 16 '13 at 13:10
  • I just checked, you can set your region in Windows and at least in Windows 8 you can even set the date format to use either m-d-y or d-m-y date formatting without changing the region. These changes effect how the dates are shown in Excel. (I had this issue once a long time ago in Windows XP, I believe there I only had the option of changing the region which fixed the formatting issue.) – Amer Apr 16 '13 at 13:18
0

I am able to get the values in Excel with the formula =A1+1

Click here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.