+ Reply to Thread
Results 1 to 8 of 8

Multiplying time value by number figure

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

    Arrow Multiplying time value by number figure

    I need help please in creating a formula for the following purpose:
    I need to subtract a fixed time value of 10:00 from a given time value figure (NN:00) from a cell (A1) then multiply the result by 1.5 and then add the 10:00 hours and finally multiply the final result by 1234
    Last edited by Khaldon; 04-28-2011 at 11:22 AM.

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Multiplying time value by number figure

    Have no clue what your doing, but try this:

    If your data is: 00:24:00

    =(((A1-TEXT("10:00","mm:ss"))*1.5)+TEXT("10:00","mm:ss"))*1234

    If your data is: 24:00:00

    =(((A1-TEXT("10:00","hh:mm"))*1.5)+TEXT("10:00","hh:mm"))*1234
    Last edited by xenixman; 04-27-2011 at 07:06 PM.

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

    Re: Multiplying time value by number figure

    Thank you for the formula, let me please clarify more what I am trying to achieve.
    I would like to reach the following result on a selected cell other than cell A1 assuming cell A1 has a time value of 36:06

    I would like the formula to contain the following values please:
    36:06 is variable, depending previously on cell A1 value
    Values 10:00, 1.5 and 1234 are fixed.

    36:06-10:00=26:06
    26:06*1.5=39:15
    39:15+10:00=49:15
    49:15*1234=60774.5

    60774.5 results are what I would like to see on my desire selected cell when inserting the right formula; the number resulted obviously subject to change depending on A1 cell value.

    The formula suggested by you gave me a result of 2528.09 instead of 60774.5
    Last edited by Khaldon; 04-28-2011 at 07:11 AM.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiplying time value by number figure

    I think your maths is a little out

    26:06*1.5 = 39:09

    given that discrepancy I think the result should be 60651.1 which you can get with this formula

    =((A1-"10:00")*24*1.5+10)*1234

    Audere est facere

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

    Re: Multiplying time value by number figure

    The following formula in cell A1 is what resulted in 36:06
    =IF(B1>3.25,A1-3.25,"")

    Using =((A1-"10:00")*24*1.5+10)*1234 formula in cell C1 resulted in 60674.24 instead of what you figured 60651.1

    What cause this difference please?

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiplying time value by number figure

    If you had that formula in A1 then you'd get a circular reference so I guess the formula should refer to a different cell....anyway working backwards if you get 60674.24 then I reckon that the starting value also has some seconds, i.e.

    36:06:45

    If the value is formatted as [h]:mm then seconds won't display but they'll be considered when using the value for calculations, do you want that or should seconds be ignored (in which case you might want to round to the nearest minute, 36:07 in this case rather than 36:06)?
    Last edited by daddylonglegs; 04-28-2011 at 10:53 AM.

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

    Re: Multiplying time value by number figure

    You were absolutely right when I reformatted my numbers to show seconds as well It reveal the hidden 45 seconds, How can I please amend this formula =SUM(L2:L57)/12 to ignore the seconds, how the formula should read?
    Last edited by Khaldon; 04-28-2011 at 11:18 AM.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiplying time value by number figure

    Try this version

    =((TEXT(A1,"[h]:mm")-"10:00")*24*1.5+10)*1234

+ 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