Calculation money entitled for official trip

S

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$.

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.50hrs, how can i calculate the money i am entitled to.
what should be the formulas and format of respective cells to calculate. Can
anyone please help me.
 
D

dlw

put your start date/time in A1, using this format mm/dd/yyy hh:mm, put your
end date/time in B1 using same format. Use formula =day(B1-A1) to get total
days of trip. use =hour(b1-a1) to get the leftover hours, multiply days by
$20, then IF statements or vlookup to get hours
 
D

Dave

Hi,
Let your Start Cell be A1
Let your Finish Cell be A2
Let your Time Away Cell be A3
Format A1:A3 with custom format dd:hh:mm
Enter "12.05.08 at 16.38hrs" in A1 in this way: 12/5/08 16:38
Enter "16.05.08 at 11.50hrs" in A2 in this way: 16/5/08 11:50
Enter =A2-A1 in A3.
A3 shows Time Away in days, hours, minutes (03:19:12)

Is this enough, or do you want XL to return the actual traveling cost?
Note that in your list, a period of 3 days 19 hours 12 minutes has no
specified cost. Am I missing something?

Regards - Dave.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top