Convert time to money

Hi there,

I need to convert time to money 1 second = 1 cent e.g. 13 seconds = 13 cents or 1 minute 29 seconds = 89 cents

However this is only up to the time of 3 minutes 50 seconds, after that it is 3 minutes 50 seconds to 5 minutes = $2

Can anyone help please?

Thank you Bel

If you have a time in A1 like 0:01:29 representing 1 minute 29 seconds then this formula will convert to $0.89

=A1*864

format as currency

I don't know if the second part makes much sense - 0:03:50 is 230 seconds so would become $2.30, then you want higher amounts to become $2.00?

If that's really what you want try

=IF(A1>"0:03:50"+0,2,A1*864)

That formula worked really well I had tested it as well to make sure it would work- but when I went to use the formula in this months spreadsheet I noticed that the format of the cell is set to general so the numbers look like this 00:00:20.

When I applied the formula I was getting $2 for every answer.

So what do I need to do to fix this - should I format my time column? I tried this but it didn't really change anything. Do I need to add another entry into the formula?


Please help me.
Quesa

Looks like your times are text formatted, try a small change to the formula

=IF(A1+0>"0:03:50"+0,2,A1*864)

That works a dream!

I also want to add the following parameters onto the formula.

Any time periods between 3.20 - 5.00 is equal to $2.00 - at the moment anything over 3.20 = $2.00.

However, if the time goes over 5.00 and up to 8.20 I would like to keep the $2 for the 1st 5.00, and then add the difference as cent per second.

So 8.00 - $3.80, 8.10 - $3.90, 8.20 - $4.00

Then after that it would be similar to the 3.20 - 5.00, but the range would be 8.20 - 10.00 would equal $4.00

Is there any way to repeat these main components while the time increases?

Thanks again
Quesa

Try this formula, format result cell as currency

=MIN(2,MOD(A1,"0:05")*864)+INT(A1/"0:05")*2

Could you please explain how that formula works?

I have another spreadsheet with different figures, and for some reason the format of my time has now change from general to some time format where 12:01:28 AM = 0:01:28 ??

How do I format it match the format? or do I have to use a new formula??

So confused - Quesa

Originally Posted by ricoandquesa

I have another spreadsheet with different figures, and for some reason the format of my time has now change from general to some time format where 12:01:28 AM = 0:01:28 ??

How do I format it match the format? or do I have to use a new formula??

So confused - Quesa

I am just bumping my message - Bump

I have tried to apply the formula

=MIN(2,MOD(A1,"0:05")*864)+INT(A1/"0:05")*2

to a cell that show the duration of a phone call and is formatted to a custom h:mm:ss

the cell looks like 0:01:24 / 12:01:24 AM

The formula I have given did work when to cell was formatted to general / text.

What formula can I use to convert this information in the same way?

I need to have the time converted in the following way -

*1 second = 1 cent e.g. 13 seconds = 13 cents or 1 minute 29 seconds = 89 cents

*time between 3 minutes 20 seconds to 5 minutes = $2
*time between 5.00 - 8.20 = $2 for the 1st 5.00 + the difference as cent per second e.g. 8.00 - $3.80, 8.10 - $3.90, 8.20 - $4.00

*time between 8.20 - 10.00 = $4
*time between 10 - 13.20 = $4 ($2 per 5 minute block) = the difference as cent per minute

So basically these main components are repeated while the time increases.

Thank you