Need help with a formula

1

Hi there,

I need a bit help with a formula.
I have a database with self composed song items. These songs will be uploaded to a website. For the song length I have two fields: Minutes (as number) and seconds (as number). With a Concatenate I can put them together to get a length-string. Also Iwith a duration field. But I need this string in a special format with leading zeros.
I get for 3 (minutes) and 4 (seconds) with a concatenate(Minutes,“:”,Seconds) as result 3:4. But I need it in the forma 03:04 (with leading zeros). The leading zero only needs to appear, if Minutes or Seconds have only one digit.
Maybe someone asks now: Why you don’t put the length as text in the table - would be much easier?
The reason is: Some of the songs have different edits in different lengths. By example:
Main Edit: 03:04
Short Edit 1: 01:20
Short Edit 2: 00:30
Stinger: 00:05
If I upload the song with it’s edits, I need to calculate the total length of all edits.
This is not possible, if I use text for the song- and edits lenngth.

Can anyone help me with the leading zero format?

Best regards
Matthias

2

You can do this with a Format() formula:

Format("{1:00}:{2:00}", Minutes, Seconds)

You could also just put your lengths as text as you said and write formulas to split it down, calculate the total, and combine it back on the fly. That could be more convenient for you to work with that like this.

image

Songs.ForEach(
  Duration.Split(':').WithName(MinAndSec,
    MinAndSec.First().ToNumber() * 60 + MinAndSec.Last().ToNumber()
  )
).Sum().WithName(TotalSeconds,
  Format(
    "{1:00}:{2:00}",
    RoundDown(TotalSeconds / 60),
    TotalSeconds % 60
  )
)

P.S. — This is illustrative. If you want to calculate more than just totals (e.g. calculate different subtotals of various rows) it may make sense to introduce a new calculated column for seconds for each clip (the Duration.Split(':')... part so that calculating a Sum() over seconds doesn’t require repeated splitting. However, this is negligible if you only run it on a small bunch of rows (100 clips or so)

3 Likes
3

Wow! That was fast!!! And it works great! Both solutions. Many many thanks to you for your help.

Best regards
Matthias

1 Like
4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.