+ Reply to Thread
Results 1 to 10 of 10

Convert time to money

  1. #1
    Registered User
    Join Date
    09-25-2007
    Posts
    38

    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

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

  3. #3
    Registered User
    Join Date
    09-25-2007
    Posts
    38
    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

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Looks like your times are text formatted, try a small change to the formula

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

  5. #5
    Registered User
    Join Date
    09-25-2007
    Posts
    38
    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

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try this formula, format result cell as currency

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

  7. #7
    Registered User
    Join Date
    09-25-2007
    Posts
    38
    Could you please explain how that formula works?

  8. #8
    Registered User
    Join Date
    09-25-2007
    Posts
    38
    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

  9. #9
    Registered User
    Join Date
    09-25-2007
    Posts
    38
    Quote 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

  10. #10
    Registered User
    Join Date
    09-25-2007
    Posts
    38

    convert time to money (time format)

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1