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
You want to convert timestamps from “walltime” (days:mins:secs) to pure “seconds”?
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.
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…)
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!”
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.
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’ represented by ‘1’ is the! one and only unit calc uses for all time calculations afaik …
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!