Date problems

  • Thread starter Thread starter Beesht
  • Start date Start date
B

Beesht

I have a work sheet which has a date column.
I want to use just m/d as the format with no year but excel 2003 is all
ways putting in the current year by its self.
I have set the cells as custom d/m but excell still puts in the current
year.
Is the a way to just have d/m and also how can I setup the whole work
sheet to use a specific year.
Thanks
 
Beesht > said:
I have a work sheet which has a date column.
I want to use just m/d as the format with no year but excel 2003 is all
ways putting in the current year by its self.
I have set the cells as custom d/m but excell still puts in the current
year.
Is the a way to just have d/m and also how can I setup the whole work
sheet to use a specific year.
Thanks
Click to expand...

All dates must contain a year, as a date is simply the number of days since
a starting point (31st Dec 1899). All that happens when you format a cell is
to change how the date is displayed. So you can choose not to display the
year, but this does not change the underlying value. You can see this
because it all works if you later change the format to include display of
the year.

If you are not going to do arithmetic on the dates, you could just use text
entries. (However, if this is the case, why would it bother you whether the
year was included or not?) If you are doing arithmetic, you must use real
dates (but you should now understand that this necessitates including the
year). If you don't put in the year yourself, Excel assumes the current
year; you cannot change that.

You could use formulas to convert all dates to this year, if that's what you
need. Something like
=DATE(2004,MONTH(A1),DAY(A1))
copied down. You could then use Copy followed by Paste Special > Values to
overwrite the original dates with the new ones.
 
Beesht
If you do need to use only the month and day in some other formula, you can use the MONTH() and DAY() functions to strip out the data within your other formulas

Then it won't matter what year is in your date column since you aren't displying it anyway

Good Luck
Mark Graesse
(e-mail address removed)

----- Paul wrote: ----

Beesht > said:
I have a work sheet which has a date column
I want to use just m/d as the format with no year but excel 2003 is al
ways putting in the current year by its self
I have set the cells as custom d/m but excell still puts in the curren
year
Is the a way to just have d/m and also how can I setup the whole wor
sheet to use a specific year
Thank
Click to expand...

All dates must contain a year, as a date is simply the number of days sinc
a starting point (31st Dec 1899). All that happens when you format a cell i
to change how the date is displayed. So you can choose not to display th
year, but this does not change the underlying value. You can see thi
because it all works if you later change the format to include display o
the year

If you are not going to do arithmetic on the dates, you could just use tex
entries. (However, if this is the case, why would it bother you whether th
year was included or not?) If you are doing arithmetic, you must use rea
dates (but you should now understand that this necessitates including th
year). If you don't put in the year yourself, Excel assumes the curren
year; you cannot change that

You could use formulas to convert all dates to this year, if that's what yo
need. Something lik
=DATE(2004,MONTH(A1),DAY(A1)
copied down. You could then use Copy followed by Paste Special > Values t
overwrite the original dates with the new ones
 
Back
Top