Formatting Dates in Excel

December 17, 2011 at 08:45:29
Specs: Windows 7
Some times I have only the year (eg 1914). Other times I have the full date (eg 12/25/2011). If I format the Excel for as a date and enter merely 1914, it shows as 3/28/1905 (its reading only the "1914"). I do NOT want to enter 1/1/1914 - because that is an incorrect date (all I have is the year 1914). What I want: If I have only the year, I want only the year (eg 1914) to show. If I have the full date, I want the full date (eg 12/25/2011) to show. How do I do it? Does it require an IF statement?

See More: Formatting Dates in Excel

Report •

December 17, 2011 at 09:42:08
Not sure what it is your after, but Excel stores dates and times as a number, representing the number of days since January 1, 1900

So when you enter 1914, Excel reads it as 1,914 days since 01/01/1900
or 03/28/1905.

See this page for a complete explanation of how Excel works with Dates/Times:


Report •

December 17, 2011 at 16:30:32
Thanks ask what it is that I want: I want to format all th ecells in a column so that if I know only the year (eg 1914), I want to enter 1914 and have "1914" [without the quotes] to show in the cell.

If I know the full date (eg 12/25/2011), I want to be able to enter the full date (eg 12/25/2011) and have the full date show in the cell (eg "12/25/2011" [without the quotes).

I do not believe there is a way to format a column of cells to work like this. I believe I have to format the year only cells as YYYY and the full date cells as MMM/DDD/YYYY. If there is a way to format the entire column of cells to show the dates as above indicated, pls advise. Thanks,

Report •

December 17, 2011 at 18:36:08
I do not believe there is a way to format a column of cells to work like this.

There is no Custom Formatting way, that I know of.
There might be a VBA solution, but I'm not very good with VBA.


Report •

Related Solutions

December 17, 2011 at 21:39:18
Try this in a back up copy of your workbook.

Right click the sheet tab for the sheet that you are using these dates in and paste the following code into the window that opens.

Note: The code assumes that your dates are being entered into Column A (1). If your dates are being entered into a different column change the number 1 in the code to the correct column number.

If you do not want to enter a date in a given cell, just click Cancel in the InputBox and enter whatever you want.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column = 1 Then
  myDate = Application.InputBox _
   ("Please Enter Full Date or 4 Digit Year", "Date Entry")
     If myDate = False Then Exit Sub
      If Len(myDate) = 4 Then
        With Target
          .Value = myDate
          .NumberFormat = "0"
        End With
        With Target
          .Value = myDate
          .NumberFormat = "mm/dd/yyyy"
        End With
      End If
 End If
End Sub

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

Report •

Ask Question