How to add accumulated days to a time format?

[ujhmh]

I want to format an accumulated time value as [DDD] HH:MM or [DDD] HH:MM:SS.
This is not a clock time, but the existing [DD] format assumes that so it interprets it based on a date.
I just want the hours to show up as modulo 24 and the Days portion to show how may integer 24 hour intervals there are. Is it possible?

Note,
adding [DD] to a time format results in the calendar date being shown, which in my case is interpreted as slightly past the epoch (i.e. pre-1900),
and if I use [DDD] as a hint that I don’t want the two-digit calendar date the result is the day of the week in shorthand (i.e. Sun, Mon, …)
and if I use [DDDD] I get the day of the week in longhand (i.e. Sunday, Monday, …).

That is clever, but not what I am looking for.

[Villeroy]

=INT(value) returns the days
=MOD(value;1) returns the remaining time.
=INT(value) & " days and " & TEXT(value;“HH:MM”) returns a text like “23 days and 13:45”

[karolus]

Hallo
The ‘D’ in formatstring are

always interpreted as ‘Day of some Month’ , it has no meaning ‘Duration in Days’.
The Solution is: take the Integerpart of your Duration as DefaultNumber-format ‘0’ and the Decimalplaces in Format ‘HH:MM’

@ujhmh

That is clever, but not what I am looking for

no its stupid to throw such a Format on Durations

[ujhmh]

I guess my only opption is to use the HHHHH:MM format since I don’t want to split off a separate ‘days’ column. Is there any chance there is some form of Libreoffice scripting language that lets you do a user-defined format at a lower level than pasting together the existing format character strings?

[karolus]

Then use Format [HH]:MM for your Durations

[ujhmh]

That is what I am using, [HH].MM.

It is interesting that the H format character can mean both ‘hour on the clock’ (as HH) and summation of hours as [HH], but the D character can only mean ‘day on the calendar’. Just say’n.

[Lupp]

If you want to calculate duaritions, it may be essential to clearly distinguish them from TimeOfDay (TOD) values by the format for the display.
The appropriate format I would recommend in this case is [H]" h "M" min "S" s".
To get aligned durations you may prefer [H]" h "MM" min "SS" s". You may also append decimals (..000 e.g.) to the SS part.
Don’t join those who misuse TOD formats for durations.

The colon-formats are only specified for TOD by ISO - and users whos disregard this often get in a trap if thy want to omit the hours part. Unfortunately many use M:SS for track durations disregarding the fact that standards don’t cover this.

Actually spreadsheets would need predefined specialized duration formats.

[ujhmh]

I’ve clearly been misusing the HH:MM:SS format since I have also used it to display non-TOD DDD:MM:SS which coincidentally is modulo 60 on the MM and SS parts, thus:
123.456789 123:27:24.44
359.456788 359:27:24.44
987.456787 987:27:24.43

[joshua4]

Here is a UDF that would be used as

=ELAPSEFORMAT(MyElapseAsDateReference)

Function ElapseFormat(DateValue As Variant)
 	Dim Days As Integer
 	Dim Hours As Integer
 	Dim Minutes As Integer
 	Dim Seconds As Single
 	Dim Rest As Variant
 	
 	Days = Int(DateValue)
 	Rest = DateValue - Days
 	Hours = Int(Rest * 24)
 	Rest = (Rest * 24 - Hours) / 24
 	Minutes = Int(Rest * 24 * 60)
 	Rest = (Rest * 24 *60 - Minutes) / (24 * 60)
 	Seconds = Int(Rest * 24 * 60 * 60 + .5) 'For unit seconds
 	Seconds = Round(Rest * 24 * 60 * 60, 2) 'For fractional seconds
	Rest = (Rest * 24 * 60 * 60 - Seconds) / (24 * 60 * 60)
	
	
	ElapseFormat = Days & IIF(Days <> 1, " Days, ", " Day, ") & Hours & IIF(Hours <> 1, " Hours, ", " Hour, ") & Minutes & IIF(Minutes <> 1, " Minutes, ", " Minute, ") & Seconds & IIF(Seconds <> 1, " Seconds, ", " Second")
	 	
 End Function

I just whipped this out…so test, test, test (and filter for bad input) before using it for payroll . I left the last Rest in their in case a person wanted to custom design for fractions. Pick which Seconds you want and comment out the other.

[MarkJeronimus]

There’s no built-in format for durations, but you can use a clever combination of formulas to achieve the same apparent result.

=CONCAT(INT(B1)," ",TEXT(B1,"HH:mm:ss"))

5.5 becomes “5 12:00:00” ^{(though it’s not automatically right-justified)}

If the cell you like to format instead contains a formula, you can embed that formula into this formula twice,
if you don’t want to use extra columns like Villeroy’s solution.