How to show more than 24 hours in Time format cells

[elnath78]

I have a cell that sum other Time values in format HH.MM.SS today the sum reached 24 hours and the total show is 00.00.00 however it correctly consider 24 hours in other cells where calculate the total price. If I force the hours (enter manually) to 25, it show a 01.00.00 and again correctly consider 25 and not 1 in the total price.

I need to either show the indication for days, for example something like 1 00.00.00 or as alternative the total hours in the sum, for example 24.00.00 or 25.00.00 I guess that the format show time intended as time of the day and not total time, so I’m open to other solutions that allows me to perform the calculation HOURS * PRICE considering the fraction of hours and minutes etc… currently the price formula is = F146 * HOURLYPRICE * 24 where HOURLYPRICE is a named cell containing the hourly price.

[andrew.comly]

DST = ? .

[Lupp]

Would you take the trouble, please, to explain?
Was this meant as the question “What does ‘DST’ mean?” ?
Well, ‘DST’ is the common abbreviation for the Anglo-American (don’t know how to distinguish exactly) euphemism “Daylight Saving Time”). See https://en.wikipedia.org/wiki/Daylight_saving_time.

[Lupp]

The time formats are basically made for dates and TOD values. Using days, months, years as units for durations is not supported at all.
However, you can (mis-)use the time formatting with H, M and S to display durations. Usable format codes for the purpose are (e.g.)
[H]" h " MM" min "SS" s" or [S]" s".
The square brackets tell the formatting routine not to apply the maximum cut needed for TOD.
Anyway you need to keep in mind that calculating durations based on results of NOW() or TODAY() cannot be reliable. Think allone of the so called DST.
(Don’t use the colon formats for durations. It may be common, but it’s a common mistake.)

[elnath78]

worked perfectly also using the dot as separator, ty! I just sum time amount not related to date or time of the day.

[Lupp]

Quoting @elnath78: “…also using the dot as separator…”
I don’t understand this. What format code, precisely, are you talking of?

The basical difference between TOD and durations can often cause surprising problems. The number formats specialised on time values suffer from the fundamental problem that they use the day (d) as their mandatory “unit of time” despite the fact that it is inapt in this role under the aspects of science/engineering. Calculating with durations we should consider to prefer the SI unit s (second).
If a cell (say A1) got any number of seconds, and you want that value to be formatted the TOD-way for special reasons, you can export it to a text by =TEXT(A1/86400;"[H]"" h ““MM”” min ““SS”” s""") e.g.
Please note that -contrary to different number formats- TOD formats always round down.

[elnath78]

I’m not using TOD at all, only enter time values and sum or multiply by the hourly price set.

[Lupp]

You still didn’t tell what format code you actually used.
If you use the specialised formats for time values, you are bound to use the day as the mandatory unit whether or not you want to do so.
This thread was about “Time format cells”.

[elnath78]

the same format in my post, I just added the square brackets around the HH and it now show 24 instead of 00

[Lupp]

You are using ‘Italian (Italy)’ locale? It’s the only one worldwide using the fullstop as the separator in TOD formats by default. A very bad idea!
Calculating time-proportional prices based on an ‘HourlyPrice’ the durations you enter might better use the hour as the unit. Using the day in this context is mostly preferred if (whether explicitly stated or not) the durations are calculated from TOD values. And then there are some complications. As long as (and where) we have the so called DST fun there are two days a year having 23 or 25 hours, e.g. - and no way to express the details consistently in a spreadsheet.

[elnath78]

I’m not sure why you stuck on TOD, I never use TOD but just time notation, our default is the colon, here I’m using the dot just cause it was so in libre office.

[Lupp]

Sorry! TOD simply is a (somehow common) abbreviation for “time of (the) day”. What I wanted to tell is:
TOD-values and durations are very different things.
The substantial difference should also be expressed by the formats used.
For TOD there is the ISO 8601 standard which should always be obeyed (mandatory separator “:” e.g.).
Durations are values of the phsical quantity “time”, shorthand often “t”. In Science and engineering it’s good practice to express values of quantities based on a single unit, mostly the SI unit, probably with a prefix for the order of magnitude (k, M; …, m, µ, … e.g.). The SI unit for time is the second (“s”).
There is a habit of people to prefer to express time values (and also angle values) using multiple units. That’s bad, but difficult too change.
Worse is that people who mix units for time values don’t apply a clear format. They often use a notation suggesting (depending on the value) there is a TOD.
Acceptable: [HH]" h “MM” min “SS” s".

[andrew.comly]

=TEXT(A1/86400;"[H]"" h ““MM”” min ““SS”” s"""
I pasted this in my cell, and also used it in the format cell user-defined box, this doesn’t work. What does “TEXT” mean?
For my monthly time mgmt spreadsheet:
What I did was tally up total time for all 28-31 days of a month and then divide by the days of that month. Doesn’t seem possible to display, and I experimented with just about every format, not restricted to time format options.

---

formulas used are:

1. Cell R751: “=SUM(R25, R50, R75, R100, R125, R150, R175, R200, R225, R250, R275, R300, R325, R350, R375, R400, R425, R450, R475, R500, R525, R550, R575, R600, R625, R650, R675, R700, R725, R750)”;
2. Cell R25, R50, … R750: “=SUM(G750:I750)”;
3. Cell E754: “=R751/$E$753”;

---

Even with the new libre office 5, there are still quite alot of simple functions that LibreOffice simply can’t perform. Hvr, it has improved alot.

[Lupp]

Current default version is 6.4.4.
The next first-digit-version to come is currently in a testing stage: V 7.0.0.0.beta2.

By the way: What do you expect of the =TEXT(... formula the above comment starts with? It only makes sense if A1 is a time value given in the technical/scientific unit s (second). If A1 holds a time value or a DateTime using the “unit” d (day) as returned by NOW() e.g. it makes no sense.