Windows 10: In CALC convert text date to number date Solved

  1.    2 Weeks Ago #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 ComputerSystem Spec


  2. Posts : 38,043
    Dual boot Windows 10 FCU Pro x 64 & current Insider 10 Pro
       2 Weeks Ago #2

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

    Edit:

    Do you mean 10111985?
      My ComputersSystem Spec

  3.    2 Weeks Ago #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 ComputersSystem Spec

  4.    2 Weeks Ago #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.

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	1 
Size:	107.9 KB 
ID:	175045

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	18 
Size:	142.0 KB 
ID:	175047
      My ComputerSystem Spec

  5.    2 Weeks Ago #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 ComputerSystem Spec

  6.    2 Weeks Ago #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 ComputerSystem Spec


  7. Posts : 38,043
    Dual boot Windows 10 FCU Pro x 64 & current Insider 10 Pro
       2 Weeks Ago #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 ComputersSystem Spec


  8. Posts : 10,741
    Windows 10 (Pro and Insider Pro)
       2 Weeks Ago #8

    lx07 said: View Post
    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 ComputerSystem Spec

  9.    2 Weeks Ago #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 ComputerSystem Spec


 

Related Threads
Pun. Just curious... Why not: "Date & Time Modified"? Whatever happened to Time? It flew by so quick... :huh: 157618
The column heading in Win 10 File Explorer is 'Date'. But which one? I've always been used in XP to the default being 'Date modified', but it seems that here it's Date created? Several re-saves have left 'Date' unchanged. 112393 -- Terry,...
Greetings, and thanks in advance for your help.. Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year + Now (probably starting in the...
Why Evaluation End Date in Windows Insider
Have the new Build 14291 Working OK. Noticed in checking Version I have an: Evaluation End Date: 7/15/2016 7:59:59 PM I know its somewhere that addresses this but I can't find it.:confused: Pete
How to indent Footnote text (between the raised number and the body text) automatically? I have word 2013, and I would like to insert an indentation 1.5 between the raised number and the text in footnotes (see figure 1) Figure 1...
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 07:39.
Find Us