# Some Excel help

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

1. ### QlimaxMr. Black

Joined:
Apr 15, 2007
Messages:
180
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. ### nebI'm a bananaOT Supporter

Joined:
Mar 29, 2007
Messages:
346
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. ### QlimaxMr. Black

Joined:
Apr 15, 2007
Messages:
180
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. ### nebI'm a bananaOT Supporter

Joined:
Mar 29, 2007
Messages:
346
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

=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. ### QlimaxMr. Black

Joined:
Apr 15, 2007
Messages:
180
0

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

6. ### nebI'm a bananaOT Supporter

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