[Solved] Help converting frames to 'HH:MM:SS.FF.00'

[RagusLive]

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!

[Zizi64]

If I understood exactly your task:

Code: Select all

=CONCATENATE(TEXT(INT(A1/30)/3600/24;"HH:MM:SS");":";TEXT((A1/30-INT(A1/30))*30;"00,00"))

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

Code: Select all

00:03:05:23,21 

Is it the desired result for you?

((The decimal separator is "," in this case, according to my locale settings ))

[RagusLive]

Code: Select all

00:03:05:23,21 

Is it the desired result for you?

((The decimal separator is "," in this case, according to my locale settings ))

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?

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.

[RoryOF]

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.

[MrProgrammer]

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.

[RagusLive]

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.

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!

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]

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!


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!

[Zizi64]

Code: Select all

=CONCATENATE(TEXT(INT(A1/30)/3600/24;"HH:MM:SS");":";TEXT((A1/30-INT(A1/30))*30;"00,00"))

explain in detail what this formula is doing,

The result string is a concatenated text of three parts.

Part 1:

Code: Select all

TEXT(INT(A1/30)/3600/24;"HH:MM:SS");

It is a conversion from FRAME unit to TIME unit. The date-time values are in DAY unit in the spreadsheet softwares, thefore you need convert this part of the data to DAY unit.
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)

Then you need to convert it to DAY unit:

Code: Select all

INT(A1/30)/3600/24

The calculated data will represent a Date-Time type numeric value. The integer part of the calculated data (in this case it equals to 0) will represent the days (date value), and the fraction part will represent the time value.
Then you need format this calculated data to TIME format:

Code: Select all

TEXT(INT(A1/30)/3600/24;"HH:MM:SS")

Part 2:

Code: Select all

":"

This is a separator character only.

Part 3:

Code: Select all

TEXT((A1/30-INT(A1/30))*30;"00,00")

Now you need get the rest of the original value. You need to subtract the integer part of the seconds from the original data (expressed in seconds):

Code: Select all

A1/30-INT(A1/30)

Then you need convert the result back to FRAME unit:

Code: Select all

(A1/30-INT(A1/30))*30

And then you need convert the numeric value to formatted string, with two decimal places:

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 ??//