[Solved] Hours over 23?

[Martin.Flynn]

I have a spreadsheet in which one column accumulates a number of hours spent training in the year using the formula "=IF(P2258=0,"",SUM(P$1906:P2258))". The cells are formatted "HH:MM:SS.00". When I get over 23 hrs, the rest of the cells show, eg "00:32:01.5". I understand why this happens but wondered if there was any way to show hours above 23, while still showing "MM:SS.00". I'd be quite happy for it to show as "D:HH:MM:SS.0" but when I try this it wants a date.

Thanks in anticipation.

[RoryOF]

Try [HH]:MM:SS.00

See section 4 in
[Tutorial] Ten concepts that every Calc user should know

[Lupp]

Dare I point to the fact again that the time formats using colons are made for TimeOfDay (as read from the watch). This kind of time is subject to arbitray regulations. Durations are a different thing giving an objective measure of reality. In scientific context they are written in a format similar to what you get in Calc using the code

Code: Select all

[hh]" h "mm" min "ss,00" s"

.

[Martin.Flynn]

Thanks RoryOF but I'm getting "###" in the result cell, with the same format applied to the referenced cells and it's not a column width error!
Thanks Lupp but I don't understand where that code should be entered. I put it in the time format but got a blank cell as the result.

[acknak]

"###" displayed in a cell means that the cell (column) is not wide enough to display the entire item. Text will overflow into adjacent empty cells if necessary but numbers and formula results cannot overflow--in which case you see ###.

For locales that use period as the decimal place indicator, the format code will look like this:
[HH]" h "MM" min "SS.00" s"

[Martin.Flynn]

Thanks acknack. Still getting "###" as result?

[RusselB]

As stated, the ### means that the column isn't wide enough.
I suggest right clicking on the letter at the top of the column, then select Auto-Column Width
There is an option to allow a bit of extra room, but I find the default works fine.

[Martin.Flynn]

Been doing that RusselB, but I still get "###", even if I make the column a page wide! Thanks.

[RoryOF]

Then verify your data type by /View /Value highlighting

[Martin.Flynn]

Still getting "###". Nice colours though! When I paste "[HH]" h "MM" min "SS.00" s" " into the format code box, the result box shows "###".

[RoryOF]

Then we have to have hands-on of a small sample file. The upload tab is below Submit on PostReply or FullEditor screens.

[RusselB]

Try

Code: Select all

[HH] "h" MM "min" SS.00 "s"

[acknak]

... I still get "###", even if I make the column a page wide!

Hmm ... I don't see how that's possible.

When you click on the cell that shows ###, what do you see in the formula field (in the toolbar area above the cell grid)? Is is something too long to fit on the screen?

Try clicking on the ### cell and then Format > Default Formatting ... what happens?

[Martin.Flynn]

The problem arises at Dec 19 2013 (row 355 on Running_walking sheet)

[Zizi64]

Your time interval values are Dates (with years and months) really.

The date-time values are allways Real numbers in the Calc program. The integer part represents the full days relative to the base date (see it in the settinds), the fraction part represents the hours, minutes and seconds of the remainder day.
The 'Time Interval' is a difference between two datetime value.

Attached your file again, the columns 'P', 'S' are formatted as dates. There are dates from 1904, and 1912 years.

[acknak]

Well, ok, I have no idea what's going on with this one. I've never seen this behavior before.

I can point out one problem: you have times in column P that seem a bit off, for example, P346 contains the time: 70177:39:52 (look at the value displayed in the formula bar). Somehow I doubt that you ran for 70k hours (2924 days).

Since you have several such values in column P, and you're trying to display a sum of those large values, the SUM may be too large for Calc's formatting function to convert and format, so you get the ### as some kind of overflow indication.

And, yes, that seems to be correct. The actual SUM(P3:353) is 55556.9632. If I try to format that as a time ([H]:MM:SS.00) then the result shows only ###.

I'd say the data need to be cleaned up before you can do any analysis with it.

[Martin.Flynn]

Zizi64, thanks, I was aware of dates somehow getting placed in front of the colon separated times. I had removed them from the rows that I had the problem with (over 23:59:59), forgetting that the formula was adding up the whole column to that point. I've removed them from the sample (only column P) and I get it in the form "24 h 18 min 39.00 s", which will be fine. However, in the main spreadsheet I'll have thousands of values to alter. Is there a quick way to do this?

[Zizi64]

Format all data as Time (HH:MM:SS), then select all data,
cut them to the clipboard (the cells will lost format),
and then paste the content of the clipboard back to the original place
- but! - paste them as UNFORMATTED text.

Then the Calc will recognise them as relative dates (= time intervals relative to the base date - 0 Years, 0 months... etc...)

[Martin.Flynn]

I pasted back as paste special-text only. Didn't work. Is there another way I can paste it unformatted?

[Zizi64]

Do you used Cut feature (Ctrl-X)? The Copy (Ctrl-C) will not reset the original formatting of the cells.

Do you have text values after inserting data?
How you set the import filter at pasting procedure? (Select column(s) in the filter panel, and set them to "English" type)

[RusselB]

I finally got a look at your spreadsheet, and the biggest problem I see is the time entries that you have manually entered.
Most of them are over 70000 hours, thus your time accumulation, which is showing the ### characters is a number that is too big... as Acknak already noted.
I suggest you go through your initial data, and ensure that your daily times are reasonable for a single day. If you format that column using [h]:mm:ss you'll quickly see which dates have unreasonable times, as anything that displays over 16:00:00 would be unreasonable, and anything that displays 24:00:00 (or higher) is impossible

[Martin.Flynn]

Sorted! Many thanks again, Zizi64, I hadn't used "text to columns", just "cut", "paste special", "text" only. I should've worked that part out as I've used it on a number of occasions previously.

Thanks to all for your help