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.

Improve this question
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, 2013 at 12:01
  • Is it possible that the cell where the error shows isn't of the format date?
    – Random IT Guy
    Apr 16, 2013 at 12:05

3 Answers 3

Reset to default
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

Improve this answer
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.

Improve this answer
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, 2013 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, 2013 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, 2013 at 13:18
0

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

Click here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .