Date but drop the year?

August 31, 2010 at 06:02:21
Specs: Windows XP
I have in column A dates from 1980 through 2010. I want to work with only the day date and monthly date. mm/dd. Howevery Excel will not drop the year date.
Date is formatted as, "mm/dd/yyyy".
Is there a "wild card" or something that I can use, like mm/dd/#### ? Or is there a way I can tell Excel to use only dd/mm ?
I'm working In Excel 2007
Thank you for any input

See More: Date but drop the year?

Report •

August 31, 2010 at 06:25:06
When you say "I want to work with only the day date and monthly date. mm/dd" do you mean that you want Excel ignore the year (e.g. 11/25/2008 is equivalent to 11/25/2010) or do you mean that you just don't want Excel to display the year, but still retain it for date related calculations, etc.?

I guess is all depends on what you plan to do with the result.

With 11/25/2008 in A1 this will display 11/25, but it won't be a date anymore, it will be text:

=MONTH(A1) & "/"&DAY(A1)

With 11/25/2008 in A1, this format will display 11/25 and the date will still be a date, it just won't display the year.

Format...Custom mm/dd

Finally, if you need it to be a date, but don't care if every Month/Day has a year of 2010, try this:

With 11/25/2008 in A1, use:

Format...Custom mm/dd

Every "date" set up like that will have the current year associated with it, e.g. 2010

Report •

August 31, 2010 at 07:42:06
Thank You very much. "=DATEVALUE(MONTH(B2) &"/"&DAY(B2))", this was what I was looking to do. You have been very helpful..
I understand the "&"; but I do not understand the "/" what dose this do?
Once again "Thank You"
Captain Randy

Report •

August 31, 2010 at 09:50:59
DATEVALUE needs a text string that looks like a date in order to return a valid "date".

The Concatenate operator (&) returns a Text string, regardless of what it is stringing together.

MONTH(11/25/2008) will return 11
DAY(11/25/2008) will return 25

DATEVALUE(11 & 25) = DATEVALUE("1125") which will return #VALUE since "1125" is not Text string that DATEVALUE knows what to do with.

However, DATEVALUE(11 & "/" & 25) = DATEVALUE("11/25") which is a valid Text string for DATEVALUE to work with.

Report •

Related Solutions

Ask Question