[Solved] Convert hours:minutes:seconds:frames to minutes

Discuss the spreadsheet application
Locked
Italiano
Posts: 8
Joined: Wed Feb 27, 2013 6:14 pm

[Solved] Convert hours:minutes:seconds:frames to minutes

Post by Italiano »

Hello,

Please help me find a way to convert data that is entered into Calc like this:

hour:minute:second:frames

to minutes. Frames represent PAL format that has 25 frames per second. For example, if I have a 'number' cell with 00:22:38:14, I'd like a neighbouring cell to display just 23. That's a rounded up number.
If it can't be done, is there a way I can sum up cells that have data in this format? The point is that I receive a lot of spreadsheets with this format and I only need to sum up cells into minutes.
My locale is Slovenian.

Many thanks!
Last edited by Hagar Delest on Mon Jul 22, 2013 9:50 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to convert hours:minutes:seconds:frames to minutes?

Post by acknak »

Ok,

LEFT(A1;FIND("+";SUBSTITUTE(A1;":";"+";3))-1) gives HH:MM:SS, 00:22:38

VALUE() converts that to a numeric time (as days), 0.0157175926

*24*60 converts days to minutes, 22.6333333333

And ROUND() gives 23

=ROUND(VALUE(LEFT(A1;FIND("+";SUBSTITUTE(A1;":";"+";3))-1)*24*60))

Maybe someone has a simpler way?
 Edit: PS: 
Oops--that's ignored the frame count. Have to fix that!
 Edit: PPS: 
Ok, so the frame count (<1sec)almost never changes the rounded number of minutes, but here it is anyway:
=ROUND((VALUE(LEFT(A1;FIND("+";SUBSTITUTE(A1;":";"+";3))-1))+TIME(0;0;VALUE(RIGHT(A1;LEN(A1)-FIND("+";SUBSTITUTE(A1;":";"+";3))))/25))*24*60)
Attachments
time+frames_to_minutes.ods
(9.54 KiB) Downloaded 425 times
Last edited by acknak on Tue Jun 11, 2013 5:15 pm, edited 2 times in total.
Reason: fix formula (red) & update attached
AOO4/LO5 • Linux • Fedora 23
User avatar
MrProgrammer
Moderator
Posts: 5280
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to convert hours:minutes:seconds:frames to minutes?

Post by MrProgrammer »

Italiano wrote:if I have a 'number' cell with 00:22:38:14, I'd like a neighbouring cell to display just 23.
Since this "number" won't match any number formats (as explained in section 5. Understanding data entry of Ten concepts that every Calc user should know), the value will be stored in the cell as text. Copy/Paste this data to the neighboring cells, then use Data > Text to Columns > Fixed Width. Split the data into three fields (1) HH:MM:SS, (2) the colon, and (3) frames. Leave the Column Type of the first and last fields as Standard; set the second Column Type to Hide. Assuming your times are now in B2,B3,… and your frame counts in C2, C3, …, use formula =B2*60*24+C2/60/25 in D2 and fill the formula down column D. Format (or style) column D as a number with no decimal places to show rounded minutes. You can sum these values. If you start with two cells containing 00:22:38:14, they'll show 23 minutes but their sum will be shown as 45 minutes since (00:22:38:14 + 00:22:38:14 = 00:45:17:03).
acknak wrote:… the frame count (<1sec) almost never changes the rounded number of minutes …
That will be true for an individual duration, but if multiple durations are summed, the frame count can affect the total. 00:01:14:00 + 00:01:15:00 (both shown as 1 minute) is 00:02:29:00 (shown as 2 minutes), but 00:01:14:20 + 00:01:15:20 is 00:02:30:15 (shown as 3 minutes when rounded).
Italiano wrote:That's a rounded up number.
Do you mean the minutes should always be rounded up, so 00:01:00:01 should be displayed as two minutes? If so you'll need to apply the ROUNDUP function, either to the individual durations in column D, or to their sum, depending on your goal.
 Edit: Another way: Copy/Paste this data to the neighboring cells, select them, Edit > Find & Replace > More options, select Current selection only and Regular Expressions, Search for ^(..):(..):(..):(..)$, Replace with =$1*60+$2+$3/60+$4/60/25, Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close. Format or style the column as a number with no decimal places to show rounded minutes. 
If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Italiano
Posts: 8
Joined: Wed Feb 27, 2013 6:14 pm

Re: How to convert hours:minutes:seconds:frames to minutes?

Post by Italiano »

Thanks, acknak and MrProgrammer.
MrProgrammer wrote:
 Edit: Another way: Copy/Paste this data to the neighboring cells, select them, Edit > Find & Replace > More options, select Current selection only and Regular Expressions, Search for ^(..):(..):(..):(..)$, Replace with =$1*60+$2+$3/60+$4/60/25, Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close. Format or style the column as a number with no decimal places to show rounded minutes. 
This seems to be the most elegant way - I like it very much.
Related question: if the last segment is hundredths instead of frames, how does the formula change?
Also, how would the formula change if I wanted to convert HH:MM:SS to MM?
By the way, it should be always rounded to the nearest integer.

Thanks.
OpenOffice 4 on Windows 7
Locked