Auto Insert Year in Date Cells

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

In one of my spreadsheets I have formatted all of column A for dates in
the MM/DD/YY format. Is it possible to do something in Excel so that I
manually type in the month and day but it automatically inserts 2004 as
the year. In other words, I would type 05/03 (May 3) and it would add 04
as the year. Any way to do that?

Peter
 
If you type only the month and day, Excel will automatically add the
current year.

So, you could change you computer's date to 2004 before you enter the
dates, and Excel will use that year (remember to change the date back
when you're finished!)

Or, enter the dates, then use Edit>Replace to replace all the 2005s with
2004.
 
Peter,

I think the only reliable way would be an event-fired macro that modified
the date. If you're willing to mess with a macro, post back. We'll write
you the code. http://www.mcgimpsey.com/excel/ as instructions for putting
in a macro.

You could change the system date to a 2004 date, let Excel put the year into
the dates you enter (as it does when you don't include the year) but I don't
recommend it.
 
That was default behavior in 2004; no year = this year. What happens when
you type 05/03 ? Do you want year 2004 next year too, or current year, or
current year's last year ?

HTH. Best wishes Harald
 
I have a way to do that, no need to use macro or change the system clock,
but a little bit much operation
after you entered all the date of the year 2005 as default, you just enter
365 in a cell and copy it, then select all the cells with the date you want
to change to 2004, from menu Edit-pastespecial-substract
.. You may get a lot of numbers, change the cell format to date at last.
 
Hi,

another work around is the following

-> Format Column A as Text
-> Enter only the DD/MM in column A,
-> Enter the following formula in the column B
"=DATE(2004,LEFT(A2,2),RIGHT(A2,2))" notice that the year in this has
been made to 2004, you can change it to what ever you want.
-> Once the formula is applied, Copy the Cells containing the formulas
and do a "PASTE SPECIAL - (ValUes and Number Formats)"
-> Finally Format the Column in "dd/mm/yy" format.


EXAMPLE
------
BEFORE YOU COPY, FORMAT "DD/MM" COL AS TEXT

DD/MM Formula
03/05 =DATE(2004,LEFT(A2,2),RIGHT(A2,2))
12/12 =DATE(2004,LEFT(A3,2),RIGHT(A3,2))

----

COPY IT AN SEE IT WORK!


Regards,
Zakir Ulla



zhy said the following on 30/06/2005 9:03 AM:
 
These techniques will be useful if the entered dates are contiguous. If
they're scattered around with other, already entered dates, it won't be
useful, as you have to manually select the dates to be changed. The OP
didn't say which was the case. Posters often don't paint a very complete
picture.
 
Back
Top