11

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?

10 Answers 10

12

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.

4
  • Exactly this. If having a 2012 in there is really a problem, then the only option is to format the column as text, and forget about using Excel's date formatting capabilities. Of course, that means also forgetting about sending birthday cards on the appropriate day...
    – Martha
    Aug 23, 2012 at 17:26
  • Thanks for the explanation! I really appreciate being able to understand the principle behind the solution.
    – Brendan
    Aug 23, 2012 at 17:28
  • So glad I learned about the numbers...I just tried to map this to an XML and it converted all of the dates to numbers and it didn't faze me!
    – Brendan
    Aug 24, 2012 at 17:09
  • 1
    If you don't want to put 2012, I would rather suggest you to keep date format, but put 1900. Date format is good in case calculation is needed in the future.
    – wilson
    Aug 28, 2012 at 7:08
5

Might need to use a few steps.

You have the full date. In a column beside it, place the following formula.

=CONCATENATE(MONTH(A1),"-",DAY(A1))

This will give you the month, a dash, the day. You may want to pretty it up, or use multiple columns.

Then, copy all of the cells with the new month-day combination.

In the next column, Paste Special, and select Values. This will create new cells without the formulas, just the values in the end.

Finally, delete the columns with the original date, and the formula from above.

1
  • Of course, use the month and day functions on the date cell. My date for testing was in A1.
    – dangowans
    Aug 23, 2012 at 17:16
3

As far as I know, this isn't possible. You can format the cell as mm/dd but if it's a date, it has 3 parts: month, day, and year. Excel will add the year for you when you enter just the month and day. You will see it as mm/dd but since a date by definition has to have a year in it to perform date arithmetic, you don't have a choice really. The only way I can think doing this is formatting it as a text field. But you will lose the benefits of the data format. What do you need this, may I ask? In most cases you can avoid using the year in your arithmetic if it bothers you.

2
  • Might just be an obsessive things I guess :P I see a 2012 in there and I KNOW that it's not supposed to be there!
    – Brendan
    Aug 23, 2012 at 17:29
  • I know what you mean. It bothers me too. If you don't plan on doing any sort of arithmetic, then a text field could be worth trying. Can't blame Microsoft though, a date has a year in it by definition. Without a year, it's no longer a date.
    – minhaz1
    Aug 23, 2012 at 18:00
2

Create two columns. In the first just put the month. In the second put the day separated by a space, using a 0 for single digit days. So, April 2 would read First Column: April, Second Column 0 2. Then Excel does not read it as a date and does not format it. :)

1

As I am sorting this data and entered month and day but not year in 2013 and 2014 I changed all new entries to 2013 then added a column and subtracted 41000 from each # to make it 1900(year) then formatted as month year after pasting values only. I found this was the easiest solution based on the info here. Thanks. Now I can sort these by month with no bias for the year.

0
  • First have nothing to the right of that column you want to fix.

  • You have to go to "Text to Columns" icon. Click on that.

  • Leave "Delimited" selected, click Next.

  • Click Other. (at this point my dates are xx/xx/xxxx with the slash between) So in the box next to "other", I put in the /, click Next

  • On the next screen - leave "General" selected and click finished.

  • Now your dates will be in 3 columns. The month column is going to look wacked out, the next column will be the day and the last the year. No worries.

  • Now, highlight the wacked out month column and right click to Format Cells, and select Text. Now your month is good.

  • Delete your year column out since you didn't want that anyway.

  • So now you have your month and your day in separate columns.

Want them together? Say the month is in B1 and the day is in B2 columns. To the right or using a blank column, an example formula is =Concatenate(b1,"/",b2) and Enter.

This will give you something that looks like 12/31. Now copy that 12/31 and paste all the way down the length of your spreadsheet/dates and there you go!

2
  • To find Text to Columns - it is the DATA tab. At the very end you will have to copy the new data and paste special, select values into a new column. now you can delete it all. (The above is when you run a report that already has mm/dd/yyyy already in it and you do not want to see the date on your spreadsheet nor do you want to see it in the menu screen when you click on it.)
    – Lorraine
    Aug 27, 2014 at 16:08
  • see the edits for additional needed info! If you do not do the edited steps, when you try to delete the original columns of dates, your new dates will reference to them and put REF in the field. So be sure to do the edits I added.
    – Lorraine
    Aug 27, 2014 at 16:27
0

Turn any full date into MMDD format using the formula =TEXT(cell,"MM/DD")

0

One option is to format the cells, and under the number tab, select "custom". From here, scroll through until you find "d-mmm" and select. It will give you the date in the "01-Nov" format, and it will not display the year in the cell itself while leaving a full date in the formula bar.

0

I found formatting the cells as 'date', choosing your category, and adding a forward slash to end of entry will work. For example, enter "6/15/" for June 15th.

I am using MS Excel 2013.

-2

Yes. Under Number/Custom, you can change the formulas. For example, you can change ... [$-409]mmmm d, yyyy;@ ... which would read as January 1, 2022, to ... [$-409]mmmm d;@ ... which would display January 1. I just played around with different formulas to figure that out. I'm sure you could do the same with others if you are looking for a different format. Just trial and error. Hope that helps.

1
  • All you're doing here is changing the way the data is displayed, it does not prevent Excel from assuming the year and storing it. The data you enter and then store will still be wrong. Apr 4, 2022 at 1:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .