|Dates and Times are stored by Excel internally as numbers, known as Date Serial numbers. The integer part of the number is the Date, the decimal portion is the Time.|
21411 is 21,410 days since Day 1 of Excel time (1/1/1900) , therefore any cell already formatted as a date is going to convert the serial number to the date represented by that number in the format chosen. 8/14/1958 is 21,410 days after 1/1/1900, so that is what gets displayed.
If you entered 21411.74598 and the cell was formatted to show both the Date and Time, you would see 8/14/1958 5:54:13 PM since .74598 of a 24 hour day is 5:54:13 PM.
Try these exercises:
Enter 2/14/11 in A1.
Enter =A1+1 in A2
Enter =A1+30 in A3
re: "i want to be able to type 021511 and have excel put in the forward slashes. "
The only way that is going to happen is if you use a macro to tell Excel to change what was entered to something else.
Let's assume you want this to happen in Column A, excluding Row 1. Right click the sheet tab for the sheet you want this to happen in and choose View Code.
Paste the following code into the pane that opens. It will read whatever is entered and add the slashes. It has to check for a 5 digit number since Excel is going to strip off the leading 0 of 021411 before dealing with the number. In actuality, you don't even need to enter it since the code will add the leading 0.
Technically, the value that appears in the cell will be a "text value" not a date. However, Excel is usually smart enough to recognize the value as a date if you try to perform a date related operation on it.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Len(Target) = 5 Then
Target = "0" & Left(Target, 1) & "/" & _
Mid(Target, 2, 2) & "/" & _
Target = Left(Target, 2) & "/" _
& Mid(Target, 2, 2) & "/" _
& Right(Target, 2)
Application.EnableEvents = True
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.