Google-sheets – How to calculate a monetary value for a given duration in minutes and seconds

formulasgoogle sheets

I have a monetary value for 1 minute like 0,10 € (10 euro cents per minute)

In Google Sheets, I have a column to enter a length of time in minutes and seconds like "xx,xx", ex. 1,30

The calculation will be "duration" x "0,10 €". but…

In Google Sheets, what is the correct formula for calculating the monetary value of each elapsed time?

Here is a spreadsheet with example

Best Answer

First, you need to decide how you enter your duration values. Remember, 1,30 minutes is not the same as 1 minute and 30 seconds - it is 1 minute and 18 seconds, since a minute is 60 seconds.

If you insist on a format of minutes,seconds, you'll need a formula to parse this as a duration. I suggest using a regular expression. This formula will split a minutes,seconds value in A2 into two cells B2 and C2:

=REGEXEXTRACT(A2, "(\d+),(\d+)")

Having that, we can calculate a decimal number of minutes (so that 1 minute and 30 seconds is displayed as 1.5 minutes). Enter this formula in D2:

=B2+C2/60

Now that we know that 1 minute, 30 seconds equals 1.5 minutes, it is easy to calculate the charge. Let's put the minute charge in cell G1, then, in C2, enter:

=D2*$G$1

... which should display as €0.15.

See the example spreadsheet I have set up.

Note: Instead of parsing a minutes,seconds string with regex, you might have some luck experimenting with Format → Number → More formats, and using the Elapsed minutes/Elapsed seconds placeholders, but the feature seems buggy and non-obvious.

Related Topic