Posts by makp

    I need to create a string for excel that will include all the criteria.
    If previous months % premium (sheet 1 column c) is less than this months %premium than apply the % lift (sheet2 D3) to the % of premium (column c) in this month to the number of VDPS (sheet2 column b). If the previous months % premium (sheet 1column c)is greater than this month than back out % lift(sheet2 D3) , if equal than leave the same. All this needs to be matched on owner ID


    I have attached a workbook to helpozgrid.com/forum/core/index.php?attachment/69062/

    Files

    • Example.zip

      (1.18 MB, downloaded 80 times, last: )

    I am able to calculate the correct number of business hours using Networking Days, however I need to convert the hours into days.


    This is the formula that I am using


    AC2= Start Time
    I2 = Finish Time
    Open time = 8am
    Close time = 8pm


    =(NETWORKDAYS(AC2,I2)-1)*(CloseTime-OpenTime)+IF(NETWORKDAYS(I2,I2), MEDIAN(MOD(I2,1),CloseTime,OpenTime),CloseTime)-MEDIAN(NETWORKDAYS(AC2,AC2)*MOD(AC2,1),CloseTime,OpenTime)


    I can format the cell to bring back hours minutes but I need it to bring back days
    Right now it will bring back 5:30:02 hh:mm:ss I need it to bring back 0.229

    I am trying to create a formula that calculate total business hours between two dates including Sat business hours. I will have a start date and end date and will take into account M-F Business hours which are 8am- 8pm and Saturday business hours 9am-6pm. I want to exclude Sunday.


    Example
    Start Date 8/14/2014 3:31pm
    End Date 8/18/2014 6:20pm


    Need to know how many business hours did that take excluding Sun. Can someone help me create a formula for this!!