Hello…
I have problem formatting time.
If (numeric) cell have value of 120 (120 days) then:
[HH]:MM give me 2880:00 (correct response)
[DD]:HH:MM 29:00:00 (wrong respponse)
I wish to get result like days:hours, but days can be more than 31!
is that possible at all with simple cell formatting?
The cell itself have pretty long formula so using / and MOD and joining result is not deserved option.
Thanks
Use another cell with
=INT(X1)&" Days and "&TEXT(X1;"HH:MM")Doesn’t work (err501)
I want result like DD.HH. But DD can be over 31.
DD formats day of month of a date, not number of days. There is no format code for number of days.
For a cell value of 120 the date is 120 days since null-date (1899-12-30), so 1900-04-29, hence DD displays 29.
So there is no solution with formatting to show numeric 120.5 as “120 days and 12 hours” or “120:12”.
There is a solution but it does not retain the number as such. You have to concatenate a string.
=INT(X1)&" Days and "&TEXT(X1;"HH:MM")I wouldn’t expect an answer in the full sense because the handling of date and time is generally a mess - in spreadsheets and beyond.
A first point to stress:
Spreadsheets (Calc included) use the fix “unit” day for everything related to “time” or “date” in any way. This despite the fact that the days as handled by the software may have (about) 24 hours in most cases, but can have 23 h or 25 h (or values I don’t know of) depending on silly adminstrative measures or unhandled differences of zone-times and the like… You can’t use the day as a reliable unit for time as a measurable as it’s used in science and engineering.
In sheets every aspect of “time” where the usage of years and months can be included must be restricted to calendaric usage. Mixing that up with “elapsed time” or “durations” in a technical sense is inacceptable. This is also the case for ISO 8601 “durations” If they aren’t reduced to the “T” part or if the DST hokum isn’t thoroughly managed.
Thus: Where time values in the range of (as mentioned) 120 d are handled, we shouldn’t expect hours to give a useful subdivision, not to speak of minutes or seconds.
Only under thoroughly controlled conditions differences of NOW()-values can be used roughly as durations.
BTW: The only reasonable NullDate (better: 1-Date) would be 1582-10-15.
Only if Excel could handle dates before 1900-01-01 at all…
This isn’t actually a suggested solution, but part of a “proof of concept” concerning considerations I made some months ago.
Generally I would prefer a class of spreadsheets (or generally calculating software) not only calculating with raw numbers, and leaving the interpretation to whomever who associates a unit / measurable / whatever with it.
My attitude is strongly influenced by my experiences as a teacher.
Have a look into the attached example to understand better what I mean.
Sorry: You need to check the contained code for the absence of malign parts, and to permit execution of document macros then.
proofOfConceptCalculationWithMeasurablevaluesInsteadOfPlainNumbers.ods (18.7 KB)
Based on that discussion seems that best is to avoid time functions.
So then I can go only with old fashion division and modulo to have reliable result.
With
=INT(A1)&" Days and "& TEXT(MOD(A1,1)*24,"###.##") &" hours"
I get perfect result.
The bottleneck is that in my case instead A1 I have pretty long formula. And if I need to edit that it’s easy to make some mess if it’s not edited same in both places.
is there a way to simplify (avoid second replication of formula)
=INT(=SUMPRODUCT(IFERROR(VLOOKUP(D50:D1000, $'ConfigurationSheet'.$B$6:$G$1000, 6, 0),0))))&" Days and "& TEXT(MOD(=SUMPRODUCT(IFERROR(VLOOKUP(D50:D1000, $'ConfigurationSheet'.$B$6:$G$1000, 6, 0),0))),1)*24,"###.##") &" hours"
Yes. Don’t be afraid of auxiliary columns. Add an intermediate cell with the formula, and reference it from the formatting one. Spreadsheets are intended to be used that way, and allow e.g. hiding columns if not needed to be shown.
In addition, that allows for easier debugging when in doubt why something calculates wrong.
See also: Avoid repeating value in IF arguments