# How to calculate a monetary value for a given duration in minutes and seconds?

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.comJan 3 at 8:46

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

## 2 Answers

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.

``````=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  • 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