Windows 10: In CALC convert text date to number date

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

:)

 

Exporting Text Messages from a Windows Phone

Thanks a lot. I used the first part of your formula

but I did not use powershell

I instead used this formula to convert your FLOOR formula

=(((A2/60)/60)/24)+DATE(1970,1,1)

That converted it to a date format, then I formatted the text using Format Cells and selected date.

Then it appeared as a normal date.

 

I can't access settings or use Microsoft Edge, I am not running a Lenovo laptop, so that fix doesn't work for me. Please assist.

I did a system restore, and it doesn't help, other than doing a fresh install, I am at a loss.

 

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

Edit:

Do you mean 10111985?

 

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

 

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.

 

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:

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

 

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.

 

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.

 

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

 

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.