Hi everyone,
I have a Calc spreadsheet I want to use to calculate the difference between two time stamps obtained from the output of another program. I have attached this spreadsheet:
Cells A6:A7 contain plain text that I pasted in from another program. Notice the formula in cell B7 doesn’t work.
However, I can edit cells A2 or A3 by pressing F2 and changing the date but this is too time-consuming.
Why won’t Calc interpret the text pasted in cells A6:A7?
What am I doing wrong?
Allan
It’s important to know how did you paste them, because the attached spreadsheet only shows the end result, and not the history.
In general, pasting as plain text probably would be helpful to you here.
And also see this FAQ.
@Mike2, I am pasting plain text as this is what is in stored in the clipboard upon pasting. Here it is:
Tue Mar 19 17:50:20.745 2019
I had a look at the faq topic you referenced and I’m not sure I understand the application; that topic discusses converting content that is already present in cells. My question is asking if it is possible that the pasted text be interpreted, according to the cell format specification, at the time of the paste operation.
The string Tue Mar 19 17:50:20.745 2019 is not a valid date+time, afaik in not any locale. However, in an en-US locale already this would be a valid date+time: Tue Mar 19 2019 17:50:20.745
So to transform, select the column’s data and invoke the Find&Replace (Ctrl+H) dialog and
Find: ^([[:alpha:]]{3} [[:alpha:]]{3} [[:digit:]]{1,2}) (.+) ([[:digit:]]{4})$
Replace: $1 $3 $2
under Other options
tick Current selection only
tick Regular expressions
Check with Find Next that the data is actually found and if so hit Replace All.
Note this also finds the displayed value in A2:A3 of the sample because the date+time is formatted such, which might be a bit confusing, but replacing that itself also works.
As I stated in my reply to Mike’s comment, I was hoping that it was possible that, upon pasting, Calc would recognize that the input data looks the same as what would appear according to the format specification for the cell, parse the contents and work out an internal time value that is then displayed according to the format specification.
I do understand that the internal representation of a time value may not be the same as what is displayed in a cell. I was hoping (perhaps in vain) that the input would also be interpreted according to the cell format category and format code.
I am copying the text from the output of another program and I am unable to modify the program. I would rather not have to do extra operations to the spreadsheet every time I paste the values. I just want to paste the text in the cells and see the calculated difference.
Number formats are display formats, not input masks.
Oh well, them’s the breaks.