In CALC convert text date to number date


  1. Posts : 3
    W10 pro
       #1

    In CALC convert text date to number date


    The field shows as text 10/11/1985. How can I convert it to number?
      My Computer


  2. Posts : 5,833
    Dual boot Windows 10 FCU Pro x 64 & current Insider 10 Pro
       #2

    Hi there. Not sure what you mean. Please explain further.

    Edit:

    Do you mean 10111985?
      My Computers


  3. Posts : 1,471
    Win10 Home x64 - 1809
       #3

    Hey chuck43,
    I don't think the Windows Calculator will convert Human Readable Date (10/11/1985) to an Epoch TimeStamp (497851200) if that's what your wanting to do. Check out his site for doing that ... Epoch Converter - Unix Timestamp Converter
      My Computers


  4. Posts : 5,478
    2004
       #4

    Excel Online will convert date 10/11/1985 to a number (31361) which is different from Epoch timestamp.

    Excel counts days since Jan 1 1900 (which = 1 not zero and current date is included) not seconds since 1st Jan 1970 like Epoch. See Excel: How Excel stores dates and times - OfficeTuts.net

    Like @Tony K said depends what number you want but you could use Calculator app to convert to Excel format by using the Date Calculation option (click on the 3 lines in top left) and working out the number of days between 30 December 1899 and whatever date you want.

    In CALC convert text date to number date-capture1.png

    In CALC convert text date to number date-capture2.png
      My Computer


  5. Posts : 3
    W10 pro
    Thread Starter
       #5

    The reason for the conversion was to put a long column of text dates, like 03/10/1998 in chronological order.

    After much searching, I found this gem on my hard drive. It works for OpenOffice Calc so it may work for Excel.

    If you want to apply a numerical format to a column of numbers in text format (for example, text "000123" becomes number "123"), do the following:

    1. Select the column in which the digits are found in text format. Set the cell format in that column as "Number".
    2. Choose Edit - Find & Replace
    3. In the Search for box, enter ^[0-9]
    4. In the Replace with box, enter &
    5. Check Regular expressions
    6. Check Current selection only
    7. Click Replace All
      My Computer


  6. Posts : 5,478
    2004
       #6

    I understand even less now than I did before.

    You don't want to use the Calculator app but the function CALC in some program you never mentioned and now you don't want to convert dates but just trim leading zeros.

    Probably better if you explain what you want to do and (more importantly) in what program in future.

    I'm not complaining as I learned about Epoch Timestamp anyway.
      My Computer


  7. Posts : 5,833
    Dual boot Windows 10 FCU Pro x 64 & current Insider 10 Pro
       #7

    Hi there. Yes, there is no setting in Calculator to convert, however there is a way to sort in Excel. You can copy a column of text dates then paste into Excel. It automatically knows they are dates. Then highlight the column and use the sort option on the Home tab > editing section of the ribbon.

    I suppose Open Office has it as well.
      My Computers


  8. Posts : 27,224
    Windows 10 (Pro and Insider Pro)
       #8

    lx07 said:
    I understand even less now than I did before.

    You don't want to use the Calculator app but the function CALC in some program you never mentioned and now you don't want to convert dates but just trim leading zeros.

    Probably better if you explain what you want to do and (more importantly) in what program in future.

    I'm not complaining as I learned about Epoch Timestamp anyway.
    Edit: there was some editing going on...

    Chuckr43 is explaining the procedure for Openoffice Calc, which is alternative to MS Excel. Not the Calculator app.
    Even in Excel, sometimes one have to change numbers which are interpreted as text in numbers...

    There are other ways, but I'm more familiar with Excel
      My Computers


  9. Posts : 5,478
    2004
       #9

    Apologies. When I said "you should explain something" that was not fair. What I should have said is "I don't understand the question".

    It wasn't clear to me CALC (with no context) meant a function in OpenOffice. As I don't use OpenOffice I'll drop out now.
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 18:31.
Find Us




Windows 10 Forums