Solved date changed in excel

January 28, 2013 at 21:39:10
Specs: Windows 7
why is it that every time i enter a date in excel sheet, it changes it to a different number. example, if i eneter 01012013 it gives me 12/30/5190 and if enter 01012013 it gives 10/17/4670. i did format the cells and its not working

See More: date changed in excel

Report •


#1
January 28, 2013 at 22:15:41
Hi
When you enter 01012013 excel does not recognise it as a date. It does see it as a number and the equivalent of that number is the date 17/10/4670.

Time according to excel begins on 1/1/1900 which equals 1.
30 Jan 2013 = 41304 and
17/10/4670 = 1012013

If you want to enter a date it needs to be in a format that excel recognises like

DD/MM/YY or M/DD/YY depending on where you are. It will also recognise
Jan 30 2013 or January 30, 2013 as a date. But the format you used will always be recognised as a number. Try entering 1 and you'll see what I mean.


Report •

#2
January 29, 2013 at 18:40:08
THANK YOU SO MUCH FOR YOUR REPLY..BUT I THINK SOMETHING HAPPENED . IF I ENTER 010113 IT GIVES ME 09/08/1927..I FORMATTED THE CELL TO DATE AND CHECKED GLOBAL SETTING AND UN-CHECKED THE 1904 SYSTEM. IF I ENTER 010213 IT GIVES ME 12/17/1927. PLZ HELP AM ABOUT TO DESTROY THIS PROGRAM.

Report •

#3
January 29, 2013 at 19:42:59
✔ Best Answer
First, please do not post in all caps. All caps is considered the internet equivalent of shouting and we don't like to be shouted at.

Second, AlteK already answered your question.

010113 is not a date as far as Excel is concerned. It's just a number. However, if you format the cell as Date, it will convert that number into a date based on how Excel stores dates internally. It will use that number as the number of days since January 0, 1900 and display it in the Date format chosen.

To understand how Excel stores dates and times, see this site:

http://www.cpearson.com/excel/datet...

If you want to prove to yourself that your numbers are the equivalent of the Dates that Excel displayed, go to this site, scroll down and enter your numbers in the Excel Serial Day Number section, 1900 Date System field, then scroll back up and see the dates that are shown in the Gregorian Calendar. They will match the dates that Excel returned.

http://www.fourmilab.ch/documents/c...

If you want 01/01/13 in a cell, then you need to enter the slashes. 010113 is not a date as far as Excel is concerned.

If you want 010113 in a cell, put a single quote in front of it, but don't expect Excel to treat it as a date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 29, 2013 at 19:48:41
No need to shout.

As I said, entering 010113 will never give you the date you want. If you want to see 1/1/2013 then that's how you would enter it. Other alternatives are

Jan 1 2013 or January 1 2013 or Jan 1, 2013 or even 1-1-13.

So, to sum up, what you are entering will never give you the result you want.


Report •

#5
January 30, 2013 at 19:02:54
thanks guys and am very sorry....thank you so much and you guys are the best

Report •

#6
January 31, 2013 at 12:59:31
hi palpharm

No harm done but have we answered your question?


Report •


Ask Question