Calc trumps Excel

users
#1

Thought I'd share an interesting experience.

I've been trying to use Office 2007 at my new job. (It is a bit of a
struggle. I'd rather use Office 2003.)

I imported a tab-delimited file into Excel without a problem. One of
the fields was in MM/DD/YYYY HH:MM tt format. I wanted to divide the
one field into a YYYY-MM-DD field and an H:MM field. Excel could NOT
do it. It could separate the fields, but Excel *insisted* they had to
be text fields (with the apostrophe mark in front). Neither the VALUE
nor the DATEVALUE fields worked.

After several minutes of being stymied, I installed LO and launched
Calc. The same formulas that didn't work in Excel, worked in Calc just
as I wanted.

So score one for Calc being able to do the job when Excel failed miserably.

#2

Or, you do not know how to do it?
(i've never worked with Excel2007, but did 'some' things in Excel2003)

#3

VALUE and TIMEVALUE apply to TEXT strings ONLY both in Excel and LibO

If you want to parse a value field you have to use different functions

How did you import the Tab delimited file to LibO?

#4

Excel could NOT
do it.

Or, you do not know how to do it?
(i've never worked with Excel2007, but did 'some' things in Excel2003)

I would never claim a possible lack of knowledge! But, in this case, I am
fairly certain it wasn't me.

I used FIND to locate the space between the date and the time. I used LEFT
to extract the characters prior to the space and when that did not produce
a numerical value, I tried both VALUE and TIMEVALUE to convert it to a
numerical value. Neither worked.

Knowing where the space between the date and the time was and determing the
LEN of the entire text string, I used MID to extract the characters
following the space. When that did not produce a numerical value, I tried
both VALUE and TIMEVALUE to convert it to a numerical value. Neither
worked.

VALUE and TIMEVALUE apply to TEXT strings ONLY both in Excel and LibO

If you want to parse a value field you have to use different functions

See above.,

How did you import the Tab delimited file to LibO?

I did not. I had already done a lot of work with the information in Excel
which I did not want to redo. I saved my work (in .xls instead of .xlsx)
and opened that in Calc.

Does Calc have issues with opening tab-delimited files?

#5

This seems to be correct, however

1) A2 '=NOW()'
2) B2 '=FIND(" ";A2;1)'
This gives problems, because the second parameter in FIND needs to be text.
The correct way to find the " " would be:
  '=FIND(" ";TEXT(A2;"DD-MM-JJJJ UU:MM:SS";1);1)'

The "DD-MM-JJJJ UU:MM:SS" is because my locale setting is Dutch

Hmmmmmz, what happens if i save this, change locate, and re-open this
doc.....

Yes, Mr Valentine, you are right, there is a lot of work to be
done....... :cry:
I Think this should be filed as a bug, wonder how OO does do this.

#6

T.R. Valentine

When you open a tab delimited file in Excel it automatically recognizes the
data types. Therefore your string e.g. 2011-02-26 10:23:47 is correctly
interpreted as a value. Therefore it is natural that using FIND, VALUE, LEFT
or any other functions would not work.

As I said in the previous post you need to use other functions.

LibO and OOo don't have any problem with tab delimited files. The only
difference is that you can not Open a tab delimited file (otherwise it is
loaded in Writer). You need to Insert, Sheet from File.

Luuk

Indeed there is a lot of work... I just found another bug: typing =now() in
a cell shows the value instead of a date (of course you can format it
yourself...)

#7

But if you make the column wider, it changes the '40600,48653' to
'26-02-11 11:40'.....

#8

That doesn't make any sense, does it? The format of the values depends on the
width of the cell???

There are only two logical options: 1) the cell width is automatically
adjusted to show the whole data (as Excel does) or 2) the data is correctly
formatted (in this case in the DATE TIME format) and only part is shown
(depending on the width of the column). The user must manually adjust the
column width.

#9

I hyjacked a bug which was, in my opinion, related
https://bugs.freedesktop.org/show_bug.cgi?id=33689

and tried to explain this behaviour... :wink:

#10

Replies are at the bottom of threads ...

#11

No, the problem is that the layout changes when the column-width is changed.
It changes from 'number' to 'date'

And, of course, we know that one can double-click to change the width,
but that was not the discussion

#12

Hi Tom

I'm sorry to say but you are wrong on this one. LibO and OOo don't
auto-resize but Excel does.

Just type =now() on any cell and watch one of the reasons why Excel is the
number one spreadsheet bar none :wink:

Cheers,
Pedro

#13

Hi :slight_smile:

If you type any number that is too long for a cell then it usually just fills
the cell with #### marks. Words will generally overlap other cells unless those
cells and then the writing gets truncated but you see the first few letters. I
can't test the "now()" function until Thursday unless i get a call-out before
then.

I tend to think that most programs are not "good" or "bad". Generally there are
certain circumstances that fit some programs better than others. MS products
are heavy and only recently been ported to systems other than reasonably new
desktop machines running Windows. LibreOffice runs on at least 4 operating
systems (counting all linux distros as 1 and all BSDs as another 1) and a much
wider variety of machines of various ages, sizes and types.

Regards from
Tom :slight_smile:

#14

Ahhh, now i see the problem at last. Please ignore my other posts in this
thread. (Thanks Luuk)
Regards from
Tom :slight_smile: