+ Reply to Thread
Results 1 to 18 of 18

Dates will not display as dates

  1. #1
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Dates will not display as dates

    Hi, my first post here = newbie!
    Excel for Mac 2011
    Whatever I try I can not get dates to display in date format, eg. 20/05/2013 is displaying at 39934. The date displays correctly in the formula bar and behaves as a date in all other respects, it just doesn't display on the sheet in the correct format. I've tried everything I can think of to reformat but to no effect.
    I've got the feeling there is a simple solution but I'll be damned etc. etc.
    Dreddi

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Dates will not display as dates

    have you changed the number format for the cells?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Dates will not display as dates

    Have you formatted as date ?

  4. #4
    Registered User
    Join Date
    01-03-2011
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Dates will not display as dates

    Hi!
    Just use: right click on the cell\choose Number\select Date and search for the desired format that suits for you.

    Cheers!

  5. #5
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Thanks JosephP, I have tried changing the number format to remove decimal place then tried changing to a date format - no joy, am still left with the number! Is this what you were thinking?

  6. #6
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Thanks Pepe, I've tried and tried changing to different date formats, I'm always left with the number displaying. I've tried cutting and pasting to different workbooks and have successfully reformatted to a date but cutting and pasting back into my original sheet returns it to a number

  7. #7
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Quote Originally Posted by Kalergi View Post
    Just use: right click on the cell\choose Number\select Date and search for the desired format that suits for you.
    Thanks Kalergi. Right click / Format Cells / change to date format - no joy, I still get a number not a date displayed

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Dates will not display as dates

    if you enter a formula in a cell do you see the formula result or the formula itself?

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Dates will not display as dates

    Any cell protection?

  10. #10
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Quote Originally Posted by JosephP View Post
    if you enter a formula in a cell do you see the formula result or the formula itself?
    I'm seeing the formula. In Preferences it is set to calculate automatically and it doesn't calculate when I manually calculate! Does this tell you something?

  11. #11
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Quote Originally Posted by Pepe Le Mokko View Post
    Any cell protection?
    No, certainly not that I am aware of. Is there a simple way to check?
    Am loving the help I'm getting here, thanks.

  12. #12
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Dates will not display as dates

    Try a simple arithmetic on the displayed number... Does it calculate or error ?
    Elegant Simplicity............. Not Always

  13. #13
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Quote Originally Posted by AndyLitch View Post
    Try a simple arithmetic on the displayed number... Does it calculate or error ?
    The formula itself is displayed, it won't calculate or error!

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Sounds like you've set the worksheet/workbook to display formulas.

    You can change that in File>Options... or on the Formulas tab in 2010.

    There's also a keyboard shortcut, CTRL + `.
    If posting code please use code tags, see here.

  15. #15
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Dates will not display as dates

    for 2011
    excel-preferences-view
    in window options uncheck the 'show formulas'

  16. #16
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Quote Originally Posted by JosephP View Post
    for 2011
    excel-preferences-view
    in window options uncheck the 'show formulas'
    Will do, thank you again.
    Last edited by Dreddi; 05-22-2013 at 09:45 AM.

  17. #17
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Dates will not display as dates

    you're welcome :-)

    please don't forget to mark the thread solved

    by the way I believe the ctrl+`shortcut key Norie mentioned is the same on the mac which may be how it got triggered originally

  18. #18
    Registered User
    Join Date
    05-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    9

    Re: Dates will not display as dates

    Thank you JosephP, you have SOLVED my problem in the face of stiff competition from other gurus, I am very grateful - I can now get on with my day job!
    Great forum. Beers all round

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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