I'm having trouble formating dates in excel

February 14, 2011 at 10:39:50
Specs: Windows XP
Formatting a column of dates in excel should be simple but i can't figure it out. I am trying to create a date column that is going to be used for keeping track of important mail when it comes in. All I want is the formatting to be: 02/14/11. i want to be able to type 021511 and have excel put in the forward slashes. whenever i put in the date 02/14/11 and hit enter it gives me these numbers: 08/14/58. Please help!

See More: Im having trouble formating dates in excel

Report •

#1
February 14, 2011 at 11:25:08
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) & "/" & _
             Right(Target, 2)
   Else:
    Target = Left(Target, 2) & "/" _
             & Mid(Target, 2, 2) & "/" _
             & Right(Target, 2)
   End If
  End If
 Application.EnableEvents = True
End Sub

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


Report •

#2
February 14, 2011 at 16:38:07
I did what you said but it's still not putting the forward slashes in.

Report •

#3
February 14, 2011 at 16:50:24
Where are you entering your data?

P.S. Feel free to toss in something that resembles a Thanks in your next response.

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


Report •
Related Solutions


Ask Question