microsoft excelmicrosoft-excel-2003
I'm putting in employees' birthdays, but they come without years for sensitivity reasons. But when I put the month and day in, Excel automatically adds 2012 as the year! Obviously this is not right. I was able to format the cells to just show month and day, but I don't like that the year is in there because it is incorrect information.
Is there any way to keep the Date format but not have a year automatically included?
If the date cells are all in one column, here's a quick and dirty way:
Assuming the dates are in A1 downwards, insert two columns to the right.
In B1, put the formula: =DATE(YEAR(A1)-4,MONTH(A1), DAY(A1))
Copy this formula down the column to recalculate all the dates from column A.
Now select and 'copy' column B (the new dates) and use 'paste as values/paste values' into column C.
Now delete the original column and the one containing the formulas to leave the new fixed dates.
If the original dates are not in neat columns you may have to do a bit more work!
Format your columns to the date format you prefer. Enter the date of 1/11/2012 into cell A1 and the date of 1/25/2012 in cell B2. Copy the following code into A2;
=DATE(YEAR(B1),MONTH(B1), DAY(B1)+1)
and copy this into B2;
=IF(DAY(B1)=10, DATE(YEAR(B1), MONTH(B1), DAY(25)), DATE(YEAR(B1),MONTH(B1)+1, DAY(10)))
Go ahead and highlight A2 and B2 to drag them down as far as you need.
Should look like this when done;
Best Answer
Short answer:
There's no way to have a date without a year, it's simply a required part of a date. Just like the second is a required part of the time of day - if you enter, say, "1:15", the actual value will be "01:15:00" (or something like that, depending on your regional settings). The only thing you can do is format the column as text, use a date format that hides the year, or tricks like using formulae to extract the day and month into another column and hiding the column with the actual date.
Explanation:
Excel stores dates as the number of days since 0 (yes, zeroeth) January 1900. So today, 23 August 2012, would be stored as 41144, and 23 August 2008 would be stored as 39683. Because of this, Excel needs to know the year in order to calculate the correct number of days. 23 August on its own is ambiguous, because it could resolve to over 100 different values (one for each year since 1900), that's why Excel adds the year itself if you omit it.
For the time of day, Excel appends a fractional part to the number of days, which represents the fraction of the 24-hour day that has elapsed. For example, 06:00 (or 6 AM) is stored as 0.25, because at that point 25% of the day has passed.
You can see this in action by entering a date and/or time (or just
=NOW()for the current date/time) into a cell in Excel and changing the format to Number. You'll also have to increase the number of decimal places to see the full precision.For example, the current time (20:19 in my timezone, as of the writing of this) is stored as 41144.846945255.