# How do you reformat text as DDD:Hr:Min

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

First time here? Check out the FAQ!

How do you reformat text as DDD:Hr:Min

1

Hello,

you can use this

```
=MID(A12,1,3)*24*60+MID(F12,5,2)*60+MID(A1,8,2)
```

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!"

1

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

`=HOUR(TIMEVALUE(A1))+MINUTE(TIMEVALUE(A1))/24+SECOND(TIMEVALUE(A1))/1440`

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]

@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)

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:

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!

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

Seen: **70 times**

Last updated: **Sep 04 '20**

How do I export a chart in an image format from LibreOffice Calc? [closed]

Are there plans for a "papercut" project for libreoffice [closed]

Is it normal for Calc goal seek to take very long? [closed]

Please refine "Search" in Calc - implement functions in Gnumeric [closed]

LibreOffice Calc will not link to external data via internet [closed]

Is there a LibreOffice .odt, .ods viewer for Android? [closed]

Why is Calc so much slower at opening/saving files than MS-Office? (win7 x64) [closed]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.

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

The source data is

004:21:43or4d:21hr:43min?Add Answeris reserved for solutions.Presseditbelow your question to add more information; also can comment an answer.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...)