Formating Last Year's Date

D

Dave

I need to enter a lot of 2005 dates. Is there any way to make the default
date format use 2005 instead of 2006? I could always enter the year aver
time I enter a date, and/or I could change Windows Date/Time to some
fictitious date in 2005, but I'd rather not do either of these.

Thanks

Dave
 
P

Pete_UK

If you enter 31/1 (without the year), then Excel will assume that you
mean the current year and will make this 31/01/2006 (maybe formatted
differently for you). If you subsequently subtract 365 from this you
will get 31/01/2005, so here's what to do:

enter your dates in the short form ignoring the year;
once you have entered all the dates, enter 365 in a blank cell;
click <copy> on this cell, then highlight all the dates;
click Edit | Paste Special | Values (check) and Subtract (check) then
OK and <esc>

You may need to format the cells how you want them, but they will all
be 2005 dates now.

Hope this helps.

Pete
 
D

Dave

Thank you Pete - it works.

Always wondered what the various Paste functions were for. Do you know
where I can get an explanation of each?

Dave
 
P

Pete_UK

Now that you know about the Subtract option, you can guess about the
Add, Multiply, Divide options. I use the Values a lot, as this enables
you to "fix" the values from a formula. The Formats option shuld be
fairly obvious - you can copy the formats applied to a block of cells
to another block of cells (similar to Format Painter). Transpose
enables you to take data arranged horizonally and copy it somewhere
else in a vertical fashion (or vice versa). I've just picked them up
over the years by trying them out, or have read the odd snippet in a
book or on these newgroups. I'm not aware of a definitive list or
explanation of them anywhere. but others may know differently ...

Hope this helps.

Pete
 
Top