+ Reply to Thread
Results 1 to 5 of 5

Excel 2013 dates not working

  1. #1
    Registered User
    Join Date
    06-08-2014
    Posts
    4

    Excel 2013 dates not working

    Hi there,
    I use Excel all day at work and got Office '13 at home.
    Now I'm trying to enter data in a spreadsheet I brought home from work, originally created in Excel 2007 and where the first column is the date. I saved it to ensure it adopts the current settings.
    I have many entries for 2013 to enter

    In the list of formats this comes up twice like this:
    14-Mar-12
    14-Mar-12

    Regardless of which on I select:
    - If I type 2/24 I get 24-Feb-14
    - If I type 2/24/13 I get 2/24/13
    - If I type 13/2/24 I get 24-Feb-13
    - If I type 2/24/15 I get 2/24/15
    - If I type 15/2/24 I get 24-Feb-15

    So this means Excel is recognizing dates entered in the 2/25/13 syntax only for the current year.

    I have Windows8 on this new laptop and if it weren't for "Start8" I'd have returned the laptop and got one with W7 on it. I'd wasted more time in a week trying to use W8 than I'd wasted in the past 15 years on earlier versions. Now I'm finding stuff like this in Office '13. It seems they have made every aspect of everything ...unnecessarily complicated, graphic'd up with silly new icons and turned 'one step' operations into a 'hoedown'.

    Anyone have any idea how to get it to accept dates in the 2/25/13 format?

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Excel 2013 dates not working

    Quote Originally Posted by jjaylad View Post
    Regardless of which on I select:
    - If I type 2/24 I get 24-Feb-14
    - If I type 2/24/13 I get 2/24/13
    - If I type 13/2/24 I get 24-Feb-13
    - If I type 2/24/15 I get 2/24/15
    - If I type 15/2/24 I get 24-Feb-15

    So this means Excel is recognizing dates entered in the 2/25/13 syntax only for the current year.
    Non sequitor. You neglect to say if any of the results are text; that is, what =ISTEXT(A1) returns. My guess: the cell values that appear as 2/24/13 and 2/24/15 are text.

    My guess is: your system short-date format is year/month/day. But the cell format is d-mmm-yy or dd-mmm-yy. That is consistent with the appearance of dates entered as 2/24 (implicitly 14/2/24), 13/2/24 and 15/2/24.

    Thus, data entered as 2/24/13/ and 2/24/15 are interpreted as text because 24 is not a valid month number.

    How do you fix it? (Rhetorical.)

    For WinXP and Win7, I would say: go to the Regional and Language Options control panel and be sure it is set for the language that you want (e.g. English(US)). Then click on Customize, Date and be sure the short-date and long-date formats are what you want.

    However, you say you have Win8. Honestly, I know nothing about it (and I hope I never need to). I can imagine that Microsoft completely reinvented the wheel with Win8. (Sigh.)
    Last edited by joeu2004; 06-09-2014 at 12:13 PM. Reason: cosmetic

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel 2013 dates not working

    Quote Originally Posted by jjaylad View Post
    It seems they have made every aspect of everything ...unnecessarily complicated, graphic'd up with silly new icons and turned 'one step' operations into a 'hoedown'.
    Amen Brother!

    Looks like something you'd expect from Fisher Price (if you get my drift).
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    06-08-2014
    Posts
    4

    Re: Excel 2013 dates not working

    Quote Originally Posted by joeu2004 View Post
    Non sequitor. You neglect to say if any of the results are text; that is, what =ISTEXT(A1) returns. My guess: the cell values that appear as 2/24/13 and 2/24/15 are text.

    My guess is: your system short-date format is year/month/day. But the cell format is d-mmm-yy or dd-mmm-yy. That is consistent with the appear of dates entered as 2/24 (implicitly 14/2/24), 13/2/24 and 15/2/24.

    Thus, data entered as 2/24/13/ and 2/24/15 are interpreted as text because 24 is not a valid month number.

    How do you fix it? (Rhetorical.)

    For WinXP and Win7, I would say: go to the Regional and Language Options control panel and be sure it is set for the language that you want (e.g. English(US)). Then click on Customize, Date and be sure the short-date and long-date formats are what you want.

    However, you say you have Win8. Honestly, I know nothing about it (and I hope I never need to). I can imagine that Microsoft completely reinvented the wheel with Win8. (Sigh.)

    The problem is it recognizes the entries for the current year as dates and for any other years as text.
    I went into my control panel and tried changing the short date format and it made no difference whatsoever.

    Please don't tell Maxisoft that all across the country ROMPER ROOM will soon have a graduating class. They'll have recruiters at all of them in hours!

  5. #5
    Registered User
    Join Date
    06-08-2014
    Posts
    4

    Re: Excel 2013 dates not working

    Tried it again but this time I re-booted and with the short date set to M/dd/yy it works now.
    Hope that helps any others with the same issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Working in Excel 2010 and not 2013
    By Jason*** in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2014, 11:28 AM
  2. Macro not working in excel 2013
    By kbka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2014, 06:58 AM
  3. Listbox not working in Excel 2013
    By dghillza in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-18-2014, 01:38 AM
  4. Working on Multiple Workbooks in Excel 2013
    By javaid in forum Excel General
    Replies: 1
    Last Post: 12-14-2013, 11:49 AM
  5. Excel 2013 Conditional Formating not working
    By ExcelWombat in forum Excel General
    Replies: 3
    Last Post: 05-28-2013, 01:22 PM

Tags for this Thread

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