+ Reply to Thread
Results 1 to 8 of 8

Time calculation based on fixed rate

  1. #1
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Post Time calculation based on fixed rate

    I would like please a formula that will look at the time value hours and minutes in cell V3 and calculate the time value result for a rate of 2:25 (two hours and twenty-five minutes for each 24 hours) of time value in cell V3.

    Example:
    If the time value in cell V3 is 72:32,
    I expect the formula to return 7:18
    Last edited by Khaldon; 03-03-2021 at 05:11 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,541

    Re: Time calculation based on fixed rate

    Assuming all the time values are numbers and not text, then it seems like a basic proportionality: 2:25/24=x/72:32 and solve for x
    x=2:25/24:00*72:32
    which results in 7:18:13 -- so maybe it isn't a simple proportionality.

    I'm not sure how you are coming up with 8:32 as the result. Help us understand how you come up with 8:32, and we should be able to help program that sequence of calculations in Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Time calculation based on fixed rate

    72:00/24=3
    3x2:25=7:15
    :32X2:25/60=1:17
    7:15+1:17=8:32

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,541

    Re: Time calculation based on fixed rate

    So, the result should be 2:25 per 24 hours for whole hours and then add 2:25 per 1 hour for the minutes. I'm not sure I understand the rationale behind the calculation, but I can represent this sequence of calculations as:

    1) To get the whole hours from the initial 72:32:00, use the FLOOR() function FLOOR(V3,TIME(1,0,0)) returns the value rounded down to the nearest hour (result 72:0:0)
    2) Multiply this by 2:25:00/24:0:0. Again if these are in V1 and V2 respectively, FLOOR(...)*V1/V2
    3) To get the minutes (and seconds) remainder subtract the FLOOR() from the original V3-FLOOR(...)
    4) Multiple step 3 by its proportionalite (V3-FLOOR(...))*V1/TIME(1,0,0)
    5) Add step 2 and step 4 together =FLOOR(...)*V1/V2+(V3-FLOOR(...))*V1/TIME(1,0,0)

    Result is 8:32:20 or 8:32 if you ignore the seconds part.

    If you are certain that is the correct sequence of calculations, that Excel formula should replicate that sequence of calculations. I don't understand why the rate multiplier is different for the hours portion and the minutes portion of the starting value, but I don't need to understand it as long as you do.

  5. #5
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Time calculation based on fixed rate

    Maybe the following example will lead to more simple formula.

    A time value of 92:30 (hh:mm) is in cell V3.

    3 days (72:00 hours) X 2:25/day =7:15
    92:30-72:00=20:30
    20:30/24:00/day X 2:25/day = 2:03
    7:15+2:03=9:18

    How can please the above two parts calculations be combined in one simple formula?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,541

    Re: Time calculation based on fixed rate

    I don't know how to make it simpler without understanding exactly what the calculation is. The new example is different from the previous example. Here's what I see in the new example:

    1) The rate for the first 72 hours is 2:25/day.
    2) The rate for any time beyond 72 hours is also 2:25/day
    3) Which all combines together to give my original proposal based on a simple proportionality =time*2:25/24:00 which is =V3*V1/V2 as originally imagined. (92:30*2:25/24:00 is, indeed, 9:18(:51)

    Combining the two examples, it looks like there are three scenarios:
    A) time less than 72 hours -- simple proportionality V3*V1/V2
    B) time greater than some threshold -- also simple proportionality V3*V1/V2
    C) time between 72 hours and threshold -- first 72 hours at 2 hours and 25 minutes per day and the remainder at 2 hours and 25 minutes per hour.

    Which can all be combined with IF ( ) function(s). Something like =MIN ( V3,"72:0:0" )*V1/V2+IF ( V3 < threshold,remainder*V1/"1:0:0",remainder*V1/V2 )

    I'm still assuming that the example in post #3 is correct. Are you certain the example in post #3 shouldn't be 7:18(:13)?

  7. #7
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Time calculation based on fixed rate

    I am so sorry for the confusion I caused you, the example in post #3 is incorrect, it was my fault I was thinking of the day as an hour and doing the calculation accordingly!

    The example in post #5 is the correct one.

    Base on the example in post #5 would be the simple formula for that please.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,541

    Re: Time calculation based on fixed rate

    Based on post #5, my proposed formula (based on a simple proportionality) would be =V3*V1/V2 where V3 has the time value, V1 contains 2:25:00 (which is really 0.10069...) and V2 contains 24:0:0 (which is actually the number 1).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Time Calculation by different rate
    By AaruJaan in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-09-2016, 06:03 AM
  2. Replies: 7
    Last Post: 11-24-2015, 11:44 AM
  3. Value at end of time series based on Rate of Return???
    By kwt890 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-11-2015, 12:03 PM
  4. Loan Calculator with Terms based on Fixed Payment not a fixed time frame.
    By cc4digital in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2012, 04:49 AM
  5. Replies: 1
    Last Post: 02-12-2012, 12:45 AM
  6. Get inputs to calculation based on column numbers if exist, if not use fixed value
    By amandaca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2011, 11:30 AM
  7. drawdown at fixed rate over set period from investment at fixed %
    By jamook in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-28-2005, 07:00 PM

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