Some Excel help

Discussion in 'OT Technology' started by Qlimax, Jun 30, 2008.

  1. Qlimax

    Qlimax Mr. Black

    Joined:
    Apr 15, 2007
    Messages:
    180
    Likes Received:
    0
    Ok, so im using 2000


    Basically what i want is

    cell e4 to take a start time (would prefer 24hour ie 1345)

    cell f4 to take an end time

    cell g4 =(f4-e4) but i want it to give me a time in minuets.

    because g5 will give a price depending on the amount of minuets (which i have already done and is set.
     
  2. neb

    neb I'm a banana OT Supporter

    Joined:
    Mar 29, 2007
    Messages:
    346
    Likes Received:
    0
    Location:
    Australia
    Use this formula in g4: =MINUTE(F4-E4)+(HOUR(F4-E4)*60)

    Set format for e4 and f4 as 24 hour time, set format for g4 as number without decimal places
     
  3. Qlimax

    Qlimax Mr. Black

    Joined:
    Apr 15, 2007
    Messages:
    180
    Likes Received:
    0
    My excell wont let me enter in 24hour time.

    also heres the price cell

    =IF((G4<=0),0,(IF((AND(G4>0,G4<=5)),1,(IF((AND(G4>5,G4<=20)),1.5,(IF((AND(G4>20,G4<=35)),3,(IF((AND(G4>35,G4<=60)),4.5,(IF((AND(G4>60,G4<=80)),5.5,(IF((AND(G4>80,G4<=100)),6.5,7.5)))))))))))))

    1-5min $1
    5-20 $1.50
    20-35 $3
    35-60 $4.50

    then every 20min after = +$1

    there probably a better way to do this other than what i have done. Ideas?
     
  4. neb

    neb I'm a banana OT Supporter

    Joined:
    Mar 29, 2007
    Messages:
    346
    Likes Received:
    0
    Location:
    Australia
    You should be able to go to Format: Cells and choose a 24 hour time format? Or you could enter a custom format... i.e. hh:mm - I thought it would work in excel 2000

    How about this for your formula:

    =IF((G4<=0),0,(IF((AND(G4>0,G4<=5)),1,(IF((AND(G4>5,G4<=20)),1.5,(IF((AND(G4>20,G4<=35)),3,(IF((AND(G4>35,G4<=60)),4.5,4.5+CEILING((G4-60)/20,1))))))))))

    Your formula levels out at 7.5 after 100minutes... if you use this one it'll keep adding $1 for every additional 20minutes after 60minutes
     
  5. Qlimax

    Qlimax Mr. Black

    Joined:
    Apr 15, 2007
    Messages:
    180
    Likes Received:
    0

    When i use this the cell displays 00:00 but, my next cell g5 works out the correct price
     
  6. neb

    neb I'm a banana OT Supporter

    Joined:
    Mar 29, 2007
    Messages:
    346
    Likes Received:
    0
    Location:
    Australia
    I think you just need to change the cell format to be a number instead of time
     

Share This Page