A single cell with Days Hours Minutes?

[MeteoricDragon]

I am looking for a way to put Days, Hours, and Minutes in a single cell. I have another cell that has a formula in it that needs to add that DaysHoursMinutesCell to the current time. like this:

Code: Select all

=Now()+DaysHoursMinutesCell

but right now, I am using 3 cells to store Days, Hours, Minutes, and adding them all in the following formula

Code: Select all

=Now()+DaysCell+TIME(HoursCell;MinutesCell;0)

Is there any way to make such a cell? Its display format would look like this:

Code: Select all

2 Days 10 hrs
1 Day 2 Hrs
4 hrs
2 Hrs 45 Min

[Lupp]

The cell can, of course, contain the numeric value for "5 days 10 hours 30 minutes 20 seconds" or in conformance with the SI:
"5 d 10 h 30 min 20 s". And adding it to the value returned by NOW() will result in the correct instant a few days later as a date-time-value which can easily be diplayed as a date followed by a TOD.
The second addend, however cannot be displayed in one of the above formats because AOO (and LibreOffice as well) doesn't respect durations (or time differences) as a generic kind of values. There is no format simply counting full days. Any format containing a day part will result in the integer part of the numeric value interpreted as a date.
Thus the value of 5 d 10 h 30 min 20 s will be displayed as a TOD on the calendaric date of 1900-01-04 - even if you don't use the colon formatting the hours, minutes, seconds part. And if you have entered 45 days you may get displayed '13' for the days because the smart software tells us 1900-02-13 generously blanketing month and year if not present in the format code.

The only way to escape this, is (as far as I know) to include full days in the hours part, 24 hours a day.
You may then enter 130:30:20 and if you provided the cell with the format code

Code: Select all

[h]" h "mm" min "ss" s"

the cell will display
130 h 30 min 20 s . The numeric value will be 5.43773148148148 written in decimal format. The support for the format code [h] in square brackets is the only concession to the fact that a duration is completely different from a DOT (or a date-time instant).

[RusselB]

Post edited due to error in code. While what I had seemed to work for me, it was a coincidence that the number of days from the formula matched the day number of the date at that time.

[Lupp]

@RusselB : I tried to verify your statement using Calc in Portable OpenOffice 3.2.0 and in installed (on Win 8.1) LibreOffice 4.3.5 as well and did not succeed. Both these tests were performed with an English (USA) locale setting and with a German one (adapting the "d" code letter to "T"). I always got calendaric dates displayed as soon as the duration was >=24 h.
Might you be able to supply a sheet demonstrating how your suggestion works, please?

[keme]

Using a single d as part of a custom time format will show the number of days.
This is a format similar to one that I am using.. the difference is that mine also shows seconds.

Code: Select all

[<1]h:mm;d.h:mm

as a custom format will show the time information you are asking about, just not with the words.

It will not show the day count. It shows the day-number part of the date.

With the "zero-date" (or "epoch") options available in OpenOffice, this will not correspond to day count. In Excel (which uses a defective calendar and the last day of 1899 for the epoch) this will look good until you reach 32 days (where it will restart at 1 to reflect the start of february ). Perhaps LibreOffice also has that option for epoch, I don't know. OpenOffice doesn't have it, so it will show "one day" as 31 (default epoch) or 2 (either of the alternate epoch options).

You can extract accumulated time info and display in a different cell by converting to text, like this (assuming the accumulated time data is in cell B1):

Code: Select all

=INT(B1)&" days, "&TEXT(B1;"hh:mm:ss.00")

... or creating the requested textual output format:

Code: Select all

=IF(B1>=1;INT(B1)&IF(B1>=2;" days ";" day ");"")&IF(B1=INT(B1);"";INT(MOD(B1;1)*24)&" hrs "&IF((B1*24)=INT(B1*24);"";ROUND(MOD(B1;1/24)*1440)&" min"))

[/size]

You probably don't want to do this conversion "inline", although it is possible. Having the time data available in a separate cell allows further calculations. As soon as you convert to text, the data cannot be easily used in calculations.
An alternative is to use two cells to display the data, one for the integer part (days) and another for the fractional part (h/m/s). With that it is possible to keep displayed data numerical.
Regardless which solution you choose, the next in line (or yourself in a couple of years) may be confused and try to calculate using the wrong source.

[acknak]

I am looking for a way to put Days, Hours, and Minutes in a single cell. I have another cell that has a formula in it that needs to add that DaysHoursMinutesCell to the current time. like this:

=Now()+DaysHoursMinutesCell

This part is no problem at all. Calc already stores dates and times compatibly as a single value: a number of days. You can simply add together a date NOW() and an elapsed time greater than one day, because they should each be quantities with the unit days.

I'm not sure I understand the rest of your question. The only other issue is how to display the resulting value.

If you add an elapsed time to a date, NOW(), you get a new date, which you can display with any standard date or date+time format.

You can display an elapsed time as hours:minutes using the standard number formatting. If you want to show a number of days, you have to use a separate cell, with a formula, to break out the days value.

Likewise, there is no way (that I know of) to directly enter an elapsed time including a number of days. Calc will properly handle entry of an elapsed time with more than 24 hours, say 34:56, but it would probably be easier to the number of days separate from the hours:minutes.

Here's a sample sheet that shows how this works.

[keme]

On revisiting this I realize that you may want an input mask (for interpreting the input correctly), and not just an output format (for displaying the data). That would be possible by a similar workaround, using formula(s) to extract the duration. Validation of the input to avoid mistakes is the challenge with that approach.

Using separate cells for each part of the input is safer and easier to handle.

Generally, spreadsheets have a decent set of formatting options, but virtually no provision for input masks.

[Lupp]

As I already told in other places I tend to judge the lack of proper duration formats and corresponding acceptance patterns being a really bad thing. There will, however, be a "interchangeability requirement" preventing any enhancement in this field. (This the opposite problem as we often see concerning silly and superfluous features added to office software.)

Taking the opportunity I made a sheet demonstrating what I see as something like a "relative optimum" in this field (accepting some complications).

The methods (formulae and formats) to use and the need of helpers I see are due to the cumbersome relations of two basically incompatible systems of handling numeric values, one of which is more than 5000 years old in its roots and has survived despite its shortcomings under the conditions of common human tenacity. (Will we be as unwavering if it comes to defend human rights? Inches and am/pm will surely survive.)

Back to the topic: See attached!
Improvements welcome!

ooo74884DateTimeAndDuration001.ods

(44.85 KiB) Downloaded 168 times

Editing:
Sorry! Uploaded wrong version! Handling the hs values overflowing into full days was not correctly implemented in version 001.
Adding an enhanced version 002 now. ERRORS STILL EXPECTED!

ooo74884DateTimeAndDuration002.ods

(51.47 KiB) Downloaded 231 times