Results 1 to 7 of 7

Thread: Date format messing around with me

  1. #1

    Date format messing around with me



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    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 ?

  3. #3
    Quote Originally Posted by rollis13 View Post
    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 !!!!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Untitled10.jpg 
Views:	8 
Size:	59.1 KB 
ID:	1494  

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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.


  5. #5
    Quote Originally Posted by NBVC View Post
    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!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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.


  7. #7
    Ok. Thank you so much!! Have a nice day!
    Stela

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •