Ask Your Question

How do you reformat text as DDD:Hr:Min

asked 2020-09-03 21:31:35 +0200

dwilcox gravatar image

Example 004:21:43 4d:21hr:43min in spreadsheet as text. I need it reformatted to time so I can perform calculate average, min, max

edit retag flag offensive close merge delete


You want to convert timestamps from "walltime" (days:mins:secs) to pure "seconds"?

igorlius gravatar imageigorlius ( 2020-09-03 22:34:21 +0200 )edit

The source data is 004:21:43 or 4d:21hr:43min?

Add Answer is reserved for solutions.

Press edit below your question to add more information; also can comment an answer.

LeroyG gravatar imageLeroyG ( 2020-09-03 22:40:17 +0200 )edit

Hmmm I missed that idea. Actually the eaxmple seems to be text, and the question is not about formatting, but about conversion.
(Who would assume that somebody communicates a time using the colon as the delimiter between number of days and number of minutes? Ther is no limit to...)

Lupp gravatar imageLupp ( 2020-09-03 23:17:31 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-09-03 22:02:16 +0200

igorlius gravatar image

updated 2020-09-04 00:24:02 +0200


you can use this


or this ( with space removal )

=MID(SUBSTITUTE(F12," ",""),1,3)*24*60+MID(SUBSTITUTE(F12," ",""),5,2)*60+MID(SUBSTITUTE(F12," ",""),8,2)

Both should give you the total number of minutes (elapsed) with Cell A1 being the cell containing the original timestamp (ddd:hh:mm)

Note: A more dynmiac way would be to use FIND with the : seperator but that migh actually be a bit more complex.

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"

edit flag offensive delete link more



Sorry. This doesn't make sense. The D or DDD in date-time formatting always refer to the calendaric day, never to a number of days being the main part of a duration.

Lupp gravatar imageLupp ( 2020-09-03 22:13:00 +0200 )edit

answered 2020-09-03 22:22:09 +0200

Lupp gravatar image

updated 2020-09-03 23:34:29 +0200

There is no way to format a time value (meaning a duration) using the (pseudo-)unit day, and in addition the traditional sexagesimal subdivisions.

You can format a duration based on the unit of 1d which is predefined if you obtained it as a difference of calendaric date-time-values only if you accept that fractions of the day are expressed by decimals then. A code doing so would be 0.0000 "d" e.g. 1 digit of the least significant decimal would stand for 8.64 s then. Using only 3 decimals would not fully allow for a resolution to minutes.

A (slightly funny) format showing the number of days and in addition the number of minutes as the numerator of a fraction with the denominator 1440 would be described by ? ?/1440 "d" since 1440 is the number of minutes in an ordinary day.

For a timevalue in (say) A1 you can get a characterstring expressing it in mixed units (d, h, min) by the simple formula
=IF(A1<0;"- ";"") & INT(ABS(A1)) & " d " & INT(MOD(ABS(A1);1)*24) & " h " & ROUND(MOD(ABS(A1)*1440;60);0) & " min".
This is not formatting, but conversion! For calculations you will need to keep the original value.

[Edit time=2020-09-03 about 21:30 UTC]
Assuming the given example is imported as text placed in cell A1, the conversion to a duration as described requires to read what should be hours a days, minutes as hours, and seconds as minutes.
This can be done by the formula
The result uses the unit d (day) independent of the format that will be used for it.
(There are alternatives, of course.)
If the imported "data" actually is numbers formatted HHH:MM:SS, the formula needs to omit the calls to the TIMEVALUE() function: =HOUR(A1)+MINUTE(A1)/24+SECOND(A1)/1440 . [/Edit]

edit flag offensive delete link more


@Lupp: 'There is no way to format a time value (meaning a duration) using the (pseudo-)unit day, and in addition the traditional sexagesimal subdivisions.'
could be an enhancement proposal, as you get durations for the difference of dates it would be nice to have appr. formattings for them,
calc limits this to either - occasionally big - numbers of hours:minutes and wrong rouded seconds?, or days with decimal fractions ... while offering 'sexagesimal format' for timestamps,
neither 105565:12:53,35 hr nor 8402,59699074074 d are easy to handle for humans as durations, we are used to 'three days and 10 hours', 'one year and three month' or similar,
i am! aware of the difficulties starting with different lengts of months's and not ending with leap seconds or fp-granularity ... would be tricky ... but 'ddd - hh:mm' or similar should be possible and nice to have?
'(pseudo-)unit day'? 'day ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-09-04 09:18:24 +0200 )edit

Imo it would be "nice" to actually implement the fundamental distinction between "point in time" (calendaric date-time with TimeOfDay) and duarations. No hope! Most people even refuse any approach to understand the conflict.

...numbers of hours:minutes and wrong rouded seconds?

TOD formats rightfully always round down.

...we are used to 'three days and 10 hours', 'one year and three months' or similar,

The example doesn't hit exactly what I mean. However: We are used to lots of bad practice (politics, applied ethics, bad or outdated terms in writing and speaking...). The conclusion cannot be forever to leave it at that.

Lupp gravatar imageLupp ( 2020-09-04 10:56:36 +0200 )edit

:-) @Lupp:
i like your fundamental thoughts ...
'Imo it would be "nice" to actually implement the fundamental distinction between "point in time" (calendaric date-time with TimeOfDay) and duarations.' - fully agree!
'TOD formats rightfully always round down.' - the rounding problem was only for seconds as single function? just tried formatting "2020-09-04 23:59:59,5" to "YYYY-MM-DD HH:MM:SS", rounds up, ... and! ... some TOD formats 'steal' the fractional seconds on edit,
'The conclusion cannot be forever to leave it at that.' - fully agree!

newbie-02 gravatar imagenewbie-02 ( 2020-09-04 15:14:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-03 21:31:35 +0200

Seen: 70 times

Last updated: Sep 04 '20