Link to home
Start Free TrialLog in
Avatar of Eric - Netminder
Eric - NetminderFlag for United States of America

asked on

Date formatting

It's that time of year, right? I'm working on a spreadsheet of expenses during 2013, and I can't figure out if there's an easy way to make my date column show 2013 when I enter 2/17 for February 17; Excel automatically wants to enter the date as 2/17/14.

Thanks!

ep
SOLUTION
Avatar of John
John
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You could also use autofill to enter the dates. Just manually enter the first correctly and drag down from the bottom right corner.

Michael
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eric - Netminder

ASKER

thinkpads_user,

Thanks, but that's what I'm trying to avoid.

Michael,

Good idea, but it won't work. The autofill leaves each row as a single date:

2/17/12
2/18/12
2/19/12
...
6/13/12
6/17/12

The problem is that I can have several entries on one date, which means I would have to change the dates as I went along (this spreadsheet will wind up being close to 1000 lines).

So autofill won't work as a solution.

ep
Part 2 of my suggestion was to use Select and Replace. This means working carefully but it does work.

... Thinkpads_User
teylyn,

I assume this means that there's no formatting trick that can change it, correct? Like setting the format to mm/dd/2013?

ep
Weeeeeellll. You CAN format with custom format

mm/dd"/2013"

and Excel will display 2013 as the year, BUT I strongly recommend not to do that. I don't think formatting a number to show a different value is ever a good idea (exception for rounding).
I used teylyn's solution, but thinkpad_user's solution also works.

Thanks to both of you for your prompt, accurate replies.
You are most welcome and I was happy to help. I face the very same issues every year-end!

Cheers, .... Thinkpads_User