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.

1

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 – user0 Jan 4 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy