Spreadsheets: Conditional Brackets

dbb, May 20, 2:26pm
I use Open Office Calc but it's very similar to M$ Excel.

Sometimes I want to format times so they appear in one column like this, but right-justified, so the second one is 6 min. 18 sec.:--


The second-to-last is 00:00:27.

It's so much easier to focus on figures of interest when there are no leading zeroes and colons.

I've experimented with conditional brackets [] but not got what I want, and get around it by sorting on the column then using the following formats:--
SS for under 1 minute
M:SS for under one hour
H:MM:SS for 1 hour and over

Is there a way to do that once for the entire column with conditional brackets, or some other way? I've studied the help files and can't figure it out, so:--


nice_lady, May 20, 8:11pm
I had a similar situation with a spreadsheet I was creating in OO Calc. Ended up having difficulty with conditional formatting so I did this:

highlight the cells you want formatted in whatever format you want such as:
SS for under 1 minute
M:SS for under one hour
H:MM:SS for 1 hour and over

then right click the cells and click 'format cells' then on the format box click 'date' then in the 'format code' box, (near the bottom), type is SS then tap OK. Then you will find the result will then show in the SS format, or the M/SS or the HH/MM/SS dependant on what you've typed in to the 'format code' box. You will find those codes will stay in the format box and you can use them for other cells. Hope this helps you.

cjdnzl, May 21, 12:09am
Just highlight the column - click on the column letter - and then choose right justify from the second row of tools. Couldn't be simpler.

allan_mac, May 21, 12:22am
I'm not sure how Open Office Calc works, but with Excel you would need to be consistent with how you are entering the data. So your 6 minutes 18 seconds should be entered as 0:06:18. Entering it as 6:18 means 6 hours 18 minutes to your spreadsheet.

Then choose a number format HH:MM:SS for the whole column, and right-justify

dbb, May 21, 3:35am
Thanks, nice-lady, I'll try that.

cjdnzl and allan-mac:-- I get the feeling you don't at all understand what I'm saying, and that nice-lady does understand.

"Just highlight the column - click on the column letter - and then choose right justify from the second row of tools. Couldn't be simpler."

Good Lord! I know that and do it, along with left justifying and centring across one column or across multiple columns as the need arises. I'm not asking how to justify things, I'm asking how to format the times (in one simple operation) so they show as mentioned. I *do* manage to format the times as required as stated in post 1:--

". by sorting on the column then using the following formats:--
SS for under 1 minute
M:SS for under one hour
H:MM:SS for 1 hour and over"

But that has to be done every time I enter new figures, so it's a pain in the butt.

I only mentioned right justifying because I can't do it here in the post, and can't enter two or more spaces and have them displayed! The figures are left-justified here because that's how the Trade Me software works -- the only way it works as far as I know.

". your 6 minutes 18 seconds should be entered as 0:06:18. Entering it as 6:18 means 6 hours 18 minutes to your spreadsheet."

You haven't imagined the figures being right-justified. Your first part is exactly what I do -- I *don't* enter it your second way, but I want it to *display* as 6:18. As far as I know there is no other way to do it other than formatting *every* new time, or entering times as plain text, but then doing sums wouldn't work.

"Then choose a number format HH:MM:SS for the whole column, and right-justify"

That's what I do as well, and it doesn't remove unnecessary leading zeroes and colons, which makes it hard to view widely differing times. Couldn't you see that in post 1? In fact, HH would display 27 seconds as 00:00:27. H is better as it would display as 0:00:27 -- one less leading zero. But I don't want any.

My query is about *formatting* the properly-entered times, so they appear the same as in post 1, except for being right justified, which I know how to do. So please, no more discussion of right-justifying.

To try to help you understand, I'll show how the times are entered on the left, and how I want them to display on the right except for actually being right-justified which I can't do here, but *can* do in my spreadsheet. So imagine it here!

Entered -- Display wanted
16:56:34 -- 16:56:34
0:6:18 -- 6:18
1:2:27 -- 1:02:27
0:53:10 -- 53:10
0:17:4 -- 17:04
0:0:27 -- 27
23:8:01 -- 23:08:01

dbb, May 21, 4:01am
Oops, the last figure in post 5 would be *entered* as 23:8:1 not 23:8:01.

dbb, May 21, 4:53am
Here's Open Office Calc's helpfile info for conditional brackets.


Conditional Brackets
You can define a number format so that it only applies when the condition that you specify is met. Conditions are enclosed by square brackets [ ].
You can use any combination of numbers and the <, <=, >, >=, = and <> operators.
For example, if you want to apply different colours to different temperature data, enter:
[BLUE][<0]#,0 "°C";[RED][>-
30]#,0 "°C";[BLACK]#,0-
All temperatures below zero are blue, temperatures in the range 0 to 30 °C are black, and temperatures higher than 30 °C are red.


Uh-huh! Computer programmers might understand how to adapt that for other purposes, but I'm a user, not a programmer.

(Note that in the above formula the TM software has split the one line into three and added dashes at the end of lines 1 & 2.)

But it *seems* to me that there might be a way, using conditional brackets, of eliminating the display of leading zeroes and colons (up to the first digit greater than 0) in a column of times, which is what I want to do. I've experimented, but just don't know how to achieve this.

Note that I don't want to eliminate leading zeroes for 1-9 minutes if the same figure has one or more hours, and I don't want to eliminate leading zeroes for 1-9 seconds if there are minutes in the same figure -- that would stuff up alignment, making the display harder to read.

So I want:--
56:09.02 not 56:9:2
9:02 not 9:2 for 0:09:02
But just 2 for only 2 seconds would be better than 02

dbb, May 21, 2:39pm
Thank you for your help, which I appreciate. I'm doing the same as you, except that I'm a pre-Windows computer-user from 1984, so I prefer using keyboard commands and use CTRL+1 to go to the formatting dialogue instead of right clicking with the mouse. But what I discovered by trying your method was that it, as you say, puts the formats used in the format box, and CTRL+1 doesn't. I always wondered why they didn't go in there, which I recall happening with M$ Excel.

So I've had a good day already, because I've learned something. Thank you.

Thanks too for the detail you provided -- I just glossed over my procedure because post 1 was getting long enough.

Anyway, one query. Do you recall what you did with conditional formatting? If so I or others might be able to crack the problem. I just don't know where to start.

Computers are supposed to speed up menial tasks, and having to reformat all new entries of less-than-1:00:00 with M:SS, then less-than-00:01:00 with S, is a menial task. I speed things up by sorting them, then reformatting to get rid of the leading zeroes and colons, but having to do it every time we enter data in the time column it's still a slow procedure that we'd be better off without.

nice_lady, May 21, 6:06pm
OK i was trying to get a cell to change colour dependant on certain factors. But it had also to be linked to dates so that <>date nothing would happen to the cell but =date the cell would conditional format, (change colour), if the user input incorrect data. Other days the cell would remain unchanged. Anyway I eventually gave up on that after investigating conditional formatting and ended up using IF statements to sort it out with an out put of "ERROR" if the user put the wrong data in . Was a bit of a nightmare.

dbb, May 22, 10:49am
Thanks, nice_lady. Could that have been because the colour was applied to a particular cell, instead of the value in it? I coloured certain cell entries one time, but when I sorted them they lost the colour and other entries gained it. I wanted the particular values to be coloured wherever they went, not the fixed cells.

Yes, I was thinking about IF-THEN statements for my needs, but I don't know enough to do whatever's required.

Obviously the best trick is to format the entire column with H:MM:SS so I can see that the times are entered properly, then after entering new stuff, have the program go to the top of the column Time then down 1 and do something like the following:

If Entry < 1 hr And > 1 min Then Format Entry M:SS
Or If Entry < 1 min Then Format Entry S
Down 1
Repeat from top

Of course it also needs to do error checking and to alert me if an entry happens to be negative and to stop after the last entry at the bottom etc., but there's not a lot to it.

I also suppose that a macro could do this, but the language for macros is above my head.

I loved the spreadsheet program Quattro Pro which I had in the early 90s when I had a 286 computer with MS-DOS, because Quattro had a wonderful feature -- plain English language for writing macros -- so I could so easily automate really arduous tasks with massive macros and just sit back and watch it going to work on my spreadsheets, opening columns and putting data in them and sorting it, and doing all sorts of things many times faster than I could do it. That's how computing should be.

I've never had that ability with Excel or Open Office Calc because of the language they use, and it seems daft to me because so few computer-users would understand programming. I've only modified a few Basic programs to alter the display or to do things a little differently, plus written those Quattro macros, but not done anything particularly difficult.

floydbloke, May 22, 5:22pm
Not sure if this is an option for you, but consider taking a stringparsing approach. E.g if column A contains your original value, then use columns B, C and D to split it into hours, minutes and seconds. (Excel has simple function for this, e.g. =minute(A1).)
Manipulate the format of these individual values to add any leading zero's or leave blank if the actual value is zero, etc. , (keeping in mind that minutes will have to be "00" and not blank if there are zero minutes but some hours, etc.), You'd also need to add your separator ":". Then reassemble (concatenate) the string in column E. If it gets too complex you could do it in steps and use a further three columns to build up the right format. When done you can hide the columns with the all the intermediary values/formats.

nice_lady, May 22, 5:23pm
In excel you can use tools/macros/record macro to record a series of actions such as mouse clicks and keyboard actions. Than save the macro. Then you can easily create a button on the form and assign a macro to that button so that the actions you recorded just happen when you click. There is no 'writing' involved.

nice_lady, May 22, 7:29pm
Oh and of course macro recording and assign to buttons etc is also a function of open office

Share this thread

Buy me a coffee :)Buy me a coffee :)