Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Excel 2007 in Windows 7 Ultimate

    I have a sheet that was created in Excel 97, or maybe earlier. The sheet tracks buy and sell transactions. Recently I noticed a discrepency and upon investigating I found taht there is a problem with the dates.

    It has always been the norm to enter dates in teh short form 06/30/10 for June 30,2010. I noticed that the dates in the formula bar are now displayed as 30/06/2010 and in most cases they display in the spreadsheet correctly. However, in the case of the transaction I was tracing from April 1, 2010 the formula bar dispays the entry as 04/01/2010 and displays it as 01/04/10 in the spreadsheet.

    The date column was formatted as custum mm/dd/yy. I tried changing the formatting but it always reads the data from the formula bar as dd/mm/yyyy and translates it that way unless the day is greater than 12.

    I am working in English (Canada) setting in Control panel.

    Does anyone have a suggestion to get back to the way things were? Or is it time to teach this old dog to think in a different format? ( I remember pencil and paper) :-)

    Larry

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    The original spreadsheet used mm/dd/yy.
    Your default is dd/mm/yy.
    The conversion from 97 incorrectly converted the dates.

    This is a best guess from my assumptions about your post.

    cheers, Paul

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Larry - What happens if you try English (United States) in Control Panel?

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Sheffield
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lb14100 View Post
    I am using Excel 2007 in Windows 7 Ultimate

    I have a sheet that was created in Excel 97, or maybe earlier. The sheet tracks buy and sell transactions. Recently I noticed a discrepency and upon investigating I found taht there is a problem with the dates.

    It has always been the norm to enter dates in teh short form 06/30/10 for June 30,2010. I noticed that the dates in the formula bar are now displayed as 30/06/2010 and in most cases they display in the spreadsheet correctly. However, in the case of the transaction I was tracing from April 1, 2010 the formula bar dispays the entry as 04/01/2010 and displays it as 01/04/10 in the spreadsheet.

    The date column was formatted as custum mm/dd/yy. I tried changing the formatting but it always reads the data from the formula bar as dd/mm/yyyy and translates it that way unless the day is greater than 12.

    I am working in English (Canada) setting in Control panel.

    Does anyone have a suggestion to get back to the way things were? Or is it time to teach this old dog to think in a different format? ( I remember pencil and paper) :-)

    Larry

    I would open excel blank.
    Set your default to mm/dd/yy

    open the 97 spreadsheet and then save as a new sheet in current excel format
    change default to dd/mm/yy
    re-save.
    Celebrate with a short trip around the room.

    HTH (errors and over-flippancy mine).

    Paul

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Sheffield
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lb14100 View Post
    I am using Excel 2007 in Windows 7 Ultimate

    I have a sheet that was created in Excel 97, or maybe earlier. The sheet tracks buy and sell transactions. Recently I noticed a discrepency and upon investigating I found taht there is a problem with the dates.

    It has always been the norm to enter dates in teh short form 06/30/10 for June 30,2010. I noticed that the dates in the formula bar are now displayed as 30/06/2010 and in most cases they display in the spreadsheet correctly. However, in the case of the transaction I was tracing from April 1, 2010 the formula bar dispays the entry as 04/01/2010 and displays it as 01/04/10 in the spreadsheet.

    The date column was formatted as custum mm/dd/yy. I tried changing the formatting but it always reads the data from the formula bar as dd/mm/yyyy and translates it that way unless the day is greater than 12.

    I am working in English (Canada) setting in Control panel.

    Does anyone have a suggestion to get back to the way things were? Or is it time to teach this old dog to think in a different format? ( I remember pencil and paper) :-)

    Larry
    Alternatively, format an entirely different cell with the appropriate date format, and reinsert the formula in the new cell by hand.
    then copy the content of the new cell over the old one, and erase the data. It may be the case that excel has a buggy conversion utility for date, so rewriting the cell with a new reference address may do the trick.

    Again HTH

    Paul

Posting Permissions

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