[Solved] Help converting frames to 'HH:MM:SS.FF.00'
[Solved] Help converting frames to 'HH:MM:SS.FF.00'
Say I have a value of 5573.21045359 frames (1/30th of a second), how do I get that to display in HH:MM:SS:FF.00?
I don't really need exactly this format as this will simply be a visual resource. It could be dots or dashes or whatever in between the numbers or could even spit out into different cells all together, just as long as it rounds down to frames with 2 decimal places
This is for me to use in a sheet I am building for my small recording studio that will allow me to structure out a song and be able to pinpoint the exact moment that each chorus, verse, etc begins and ends. I built a sheet in excel several years ago that did this nicely, but that sheet and any memory of how I did it has long since dried up.
For this new sheet I already have all the rest of the math and formatting sorted out, I just can't seem to search well enough to find any resources on this particular issue. I found plenty for converting FROM HH:MM:SS:FF, but non for converting TO it.
Any help or guidance is greatly appreciated! Thanks!
I don't really need exactly this format as this will simply be a visual resource. It could be dots or dashes or whatever in between the numbers or could even spit out into different cells all together, just as long as it rounds down to frames with 2 decimal places
This is for me to use in a sheet I am building for my small recording studio that will allow me to structure out a song and be able to pinpoint the exact moment that each chorus, verse, etc begins and ends. I built a sheet in excel several years ago that did this nicely, but that sheet and any memory of how I did it has long since dried up.
For this new sheet I already have all the rest of the math and formatting sorted out, I just can't seem to search well enough to find any resources on this particular issue. I found plenty for converting FROM HH:MM:SS:FF, but non for converting TO it.
Any help or guidance is greatly appreciated! Thanks!
Last edited by RagusLive on Mon Mar 21, 2016 5:43 am, edited 1 time in total.
Apache OpenOffice 4.1.1 - Windows 7
Re: Help converting frames to 'HH:MM:SS.FF.00'
If I understood exactly your task:
Where cell A1 contains the value 5573.21045359 frames based on the 1/30 frame/s rate value.
The result is a TEXT (you can not make any calculation with it):
Is it the desired result for you?
((The decimal separator is "," in this case, according to my locale settings ))
Code: Select all
=CONCATENATE(TEXT(INT(A1/30)/3600/24;"HH:MM:SS");":";TEXT((A1/30-INT(A1/30))*30;"00,00"))
The result is a TEXT (you can not make any calculation with it):
Code: Select all
00:03:05:23,21
((The decimal separator is "," in this case, according to my locale settings ))
Last edited by Zizi64 on Sat Mar 19, 2016 7:59 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Help converting frames to 'HH:MM:SS.FF.00'
Very close! I just had to change the "," to a "." and the arithmetic worked perfectly! I guess that's just because of my local format settings?Zizi64 wrote:Is it the desired result for you?Code: Select all
00:03:05:23,21
((The decimal separator is "," in this case, according to my locale settings ))
One problem though; when I try to reference a cell on another sheet it fails, displaying #VALUE. I reference back and forth between sheets in other cells withing this book so I thought I was doing it correctly, is there some special formatting I should to to get this done within this formula? (I could re-build my sheet a bit to compensate if this is not possible, but it seems like it should be)
Either way thank you very much! That formula is certainly beyond my current understanding and even if I had come across it in searching I probably would not have recognized it as a solution.
Apache OpenOffice 4.1.1 - Windows 7
Re: Help converting frames to 'HH:MM:SS.FF.00'
Zizi64's formula is breaking your number into the values needed for display, the converting these numbers back into text to concatenate them together.
It is likely that your references to cells in another sheet are to the wrong type of data - strings instead of numbers or contrariwise.
Turn on /View /Value Highlighting to see what are the types of the referenced data - numbers are blue, text black and results of calculations green.
It is likely that your references to cells in another sheet are to the wrong type of data - strings instead of numbers or contrariwise.
Turn on /View /Value Highlighting to see what are the types of the referenced data - numbers are blue, text black and results of calculations green.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
- MrProgrammer
- Moderator
- Posts: 5280
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Help converting frames to 'HH:MM:SS.FF.00'
Hi, and welcome to the forum.
Previous discussion about working with Timecode values in Calc:
[Solved] Convert hours:minutes:seconds:frames to minutes
How to set time to TIMECODE ?
[Solved] User-fefined Time format
If this solved your problem 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.
Previous discussion about working with Timecode values in Calc:
[Solved] Convert hours:minutes:seconds:frames to minutes
How to set time to TIMECODE ?
[Solved] User-fefined Time format
If this solved your problem 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).
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).
Re: Help converting frames to 'HH:MM:SS.FF.00'
I just re-opened the sheet and tried to replicate the problem I was having, and I can't seem to do it. So, likely I just had something mis-typed that I was continually looking over. Which is just fine, because it seems to be working now!RoryOF wrote:Zizi64's formula is breaking your number into the values needed for display, the converting these numbers back into text to concatenate them together.
It is likely that your references to cells in another sheet are to the wrong type of data - strings instead of numbers or contrariwise.
Turn on /View /Value Highlighting to see what are the types of the referenced data - numbers are blue, text black and results of calculations green.
Those links cover converting from HH:MM:SS:FF whereas I was trying to convert to it. I'm sure there are elements of what they were doing there that could help or solve the issue for me, but as a novice I was not able to translate what they did into what I was trying to do. Zizi64 spelling it out for me was exactly what I needed. And now that I have it sorted and fully working (cannot replicate the one issue I had) I will happily mark it solved! Thanks to all here for their interest and help!MrProgrammer wrote:Hi, and welcome to the forum.
Previous discussion about working with Timecode values in Calc...
If this solved your problem 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.[/size]
Also, if anyone cars to explain in detail what this formula is doing, like in a peice-by-peice way that they think I might understand, I would greatly appreciate it. If not then no worries, I've got a working sheet and that's all I needed!
Apache OpenOffice 4.1.1 - Windows 7
Re: [SOLVED] Help converting frames to 'HH:MM:SS.FF.00'
Code: Select all
=CONCATENATE(TEXT(INT(A1/30)/3600/24;"HH:MM:SS");":";TEXT((A1/30-INT(A1/30))*30;"00,00"))
The result string is a concatenated text of three parts.explain in detail what this formula is doing,
Part 1:
Code: Select all
TEXT(INT(A1/30)/3600/24;"HH:MM:SS");
First convert from FRAME to SECONDS (based on the 1/30 frame/sec value), then get the integer part of the seconds:
Code: Select all
INT(A1/30)
Code: Select all
INT(A1/30)/3600/24
Then you need format this calculated data to TIME format:
Code: Select all
TEXT(INT(A1/30)/3600/24;"HH:MM:SS")
Code: Select all
":"
Part 3:
Code: Select all
TEXT((A1/30-INT(A1/30))*30;"00,00")
Code: Select all
A1/30-INT(A1/30)
Code: Select all
(A1/30-INT(A1/30))*30
Code: Select all
TEXT((A1/30-INT(A1/30))*30;"00,00")
Finally, the function CONCATENATE will combine, concatenate the thee parts and will return with the result string.
//Otherwise I do not understand, what will represent the fraction part of a FRAME ??//
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.