help with formula

[Sunny]

hi,
I want to calculated travelling cost depending on the days of travel. I have
following data.
(0-7)hrs-0$,
(8-13)hrs-5$,
(14-23)hrs-10$,
24hr-20$.

Now suppose I started the travel on 12.05.08 at 16.38hrs and my journey ends
on 16.05.08 at 11.30hrs, how can i calculate the money i am entitled to.
I did it this way.
*I used 24hrs time format in cell and subtracted 16.38hrs from it to get the
no. of hrs of travel on first day.
*Than i used the formula to calculate the (number of days between two
dates)-1 to get the no.of days between the dates excluding first and second
day.
*Then I used 0:00+last day time to get the number of hrs for the last day.
Note:I defined first and third cell in 13.30 time format. I dont know of its
right to do that.
*when i had no. of hrs in first cell, i tried to use and if statement to
decide upon the
money i am entitled to by using formula
=IF(S2<8:00,0,IF(S2<14:00,5,IF(S2<24:00,10:00,20)))
S2 is the the no. of hrs of travel i did on first day.its in 13.30 hr time
format
I defined this cell as number. and
I am getting error.

Can anyone help me with solution please.
Thank you
With Best Regards
Sunny

 

[Sandy Mann]

Your requirements seem a bit ambiguous. For example the elapsed time in
your example is 3 days, 18 hours and 52 minutes with 7 hours 22 minutes on
the first day. Does the 7:22 count against the 0-7 hours leaving 11:52 to
count against the 8 - 13 hour rate (ie 11:30 + the 22 minutes from the start
day), or do you simply use the 18 hours 22 minutes an apply it to the 14 -
23 hour rate? What would happen if there was not more than 7 hours on the
first day?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk