+ Reply to Thread
Results 1 to 4 of 4

Excel 2003 date formatting error(date appears one way but is read another way)

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Excel 2003 date formatting error(date appears one way but is read another way)

    I have two sheets both with a name column and a dates column. The 2nd sheet has months listed for all of 2011. The 1st sheet has months listed in 2011 (that illustrate what month a certain action occured for a certain employee, employees may have more actions one month than the next). ((Essentially what I am trying to do is put all this data into access run a query that links the name and months so all the data is tallied per month.))

    Now the problem I am having is on sheet 1 I have a custom date format (ex. 2011-01) so it can match the format in sheet 2. the only problem is for sheet one I orginally got the dates in (ex. 01/19/2011) format. Even though I entered a custom format so it appears that the dates match, Access still reads in the original format thus I cannot link all my corresponding data. I have tried copying and pasting just the values and basically everything possible. I attached an example below to make it much simpler to understand, if you look at a date in sheet 1 it appears (2011-01) but if you click on the date in the forumla bar it appears (01/19/2011). I need the formula bar to also appear (2011-01). I feel like this should not be that hard but it has stumped me, thank you for all help in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Excel 2003 date formatting error(date appears one way but is read another way)

    Hi cajodonn,

    I have checked the attachment and found that on the sheet "File that Needs dates changing" you have the dates in DATE format but on the sheet "Correct Main File" you have the DATE in Text Format i.e, manually entered year and month

    Now I have used the TEXT function in the first sheet and it is not matching :-

    =TEXT(B2,"YYYY-mm")

    see attachment :-Excel date changer.xls


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Excel 2003 date formatting error(date appears one way but is read another way)

    Thank you very much worked perfectly!

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Excel 2003 date formatting error(date appears one way but is read another way)

    You are welcome cajodonn..

    Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1