2

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

migrated from superuser.com Jan 3 at 8:46

This question came from our site for computer enthusiasts and power users.

0

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.

0

=IF(ISNUMBER(B5),B5*C2,
 PRODUCT(MINUTE(TIME(0,SPLIT(B5,","),INDEX(SPLIT(B5,","),1,2)))
       +(SECOND(TIME(0,SPLIT(B5,","),INDEX(SPLIT(B5,","),1,2))))/60,C2))

the ultimate formula which allows to input any logical format:

| 1   | 2    | 6    | 70   | -3    | ... natural numbers
------+------+------+------+-------+----
| 1,2 | 2,30 | 6,09 | 5,70 | 54,61 | ... comma separated
------+------+------+------+-------+----
| 0.5 | 0.25 | 0.75 | 2.02 | 6.86  | ... decimal numbers

test it here

x

4

  • This calculates the charge for 1.30 minutes - not 1 minutes and 30 seconds. – Vidar S. Ramdal Jan 3 at 9:42
  • @VidarS.Ramdal thx for heads up, I updated my answer – MARK MY ANSWER Jan 4 at 1:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.