Date format messing around with me

stelazet

New member
Joined
Jul 10, 2013
Messages
4
Reaction score
0
Points
0
Hi everybody!

I'm googling around since days and then I thought I found a solution, but I just noticed I didn't so I wonder if you could help me. My home date format is dd/mm/yyyy, so I have it set like that in Control panel, but this one program demands mm/dd/yyyy, so that's what I'm trying to do in Excel now.
I am also attaching an excel Table, so you see where my problem is ..

Problem 1: I have a column in a file where I should put in birthdays in MM/DD/YYYY format. Since I didn't find it in a Date format (only in English (US) I found it similar, but it was MM.DD.YY format), I decided to make it custom: mm/dd/yyyy.
I've selected the whole column and changed the cell format in the whole column. While I'm typing the dates in, I started noticing different things:
1. sometimes I type in 01/09/1957 and that's how it stays in the fx line
2. sometimes I type in 08/20/1956 and that's how I see it in a cell, but when I click on it, the fx line says 20.8.1956, which is now dd/mm/yyyy format, so still no damage - but still - why are they different in fx??
3. sometimes I type in 09/04/1984 (4th September) and when I push enter, the typing in the cell changes automatically to 04/09/1983 and in the fx line I then see 9.1.1984 --> which is now suddenly 9th April instead of 4th September !!

I lost the war with that, have no clue what to do - especially I don't understand, because I've selected the whole column and changed the cell format in all of the cells at the same time, with the same command. Why do they react differently? And what can I do against it?

Problem 2: I also have another column, where I'm supposed to enter only MM/DD. I made cell format Custom mm/dd. What happens is, that I enter 03/30 in it and in the cell that's what I see, but in the fx line it says 30.3.2013 (it adds current year straight away). I don't want that, since that's birthdays and I just wanted to have dates in it. How can I change that?

Problem 3: And if I'm here already I might just ask one more (I promise I couldn't find anything close answering those questions).
I was wondering if I can sort birthdays (mm/dd/yyyy) according to months, and although it's sorting my Custom Date format from A to Z (don't know why?) it sorts according to months, ignoring the yyyy somehow (which is great, just what I need!). Then later I wanted to sort another column where the dates are in Date Format: 26.1.2013, and it sorts them according to the year. I'm confused. Why the difference?

Thank you for your help & wish you a great day!
Stela
 

Attachments

  • Test Bdays.xlsx
    12.1 KB · Views: 20
Your column D is formatted with a personal: mm\/dd\/yyyy and column E: mm\/dd , are they special formats for Excel 2007/2010 or are they typos ?
 
Your column D is formatted with a personal: mm\/dd\/yyyy and column E: mm\/dd , are they special formats for Excel 2007/2010 or are they typos ?
Hi rollis13,

thank you to try to help! :) But I am not sure what you are asking .. ? On my computer I've formatted them Custom and here it says. mm/dd and mm/dd/yyyy. No \/signs .. ?? Attaching you a screen shot.

Thank you !!!!
 

Attachments

  • Untitled10.jpg
    Untitled10.jpg
    59.1 KB · Views: 16
Try selecting one column with dates, then go to Data|Text to Columns...then skip to 3rd window. Select Data from the "column data format" area... then select appropriate format in adjacent dropdown... then click Finish. See if that helps.

If so, select each other column, one at a time and repeat process. You may then need to format accordingly.
 
Try selecting one column with dates, then go to Data|Text to Columns...then skip to 3rd window. Select Data from the "column data format" area... then select appropriate format in adjacent dropdown... then click Finish. See if that helps.

If so, select each other column, one at a time and repeat process. You may then need to format accordingly.

Thank you NBVC!! :) After several forums and loosing sanity while googling, this now finally helped me! :) One more question - you said I may need to format then accordingly - when I select "Column data format" I actually already format accordingly, or did you mean something else?

Kind regards! :)
 
I meant, if the TExt to Column somehow changed your 8/10 to 8/10/1959 you would need to reformat back to mm/dd. If everything looks right, no need to format again.
 
Back
Top