Ask Your Question
0

Calc: Problems with pasting text in a cell formatted as time [closed]

asked 2019-03-20 15:08:13 +0200

Allan gravatar image

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:

C:\fakepath\difftime.ods

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-03-21 13:55:31.566076

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-20 15:18:33 +0200 )edit

@Mike, 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.

Allan gravatar imageAllan ( 2019-03-20 17:18:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-20 15:54:39 +0200

erAck gravatar image

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.

edit flag offensive delete link more

Comments

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 ...(more)

Allan gravatar imageAllan ( 2019-03-20 17:39:45 +0200 )edit

Number formats are display formats, not input masks.

erAck gravatar imageerAck ( 2019-03-20 19:40:06 +0200 )edit

Oh well, them's the breaks.

Allan gravatar imageAllan ( 2019-03-20 20:58:20 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-03-20 15:08:13 +0200

Seen: 28 times

Last updated: Mar 20