Hi Everybody!
Im a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If Im over the first
10 minutes I have to collect a full hour. I need help to calculate this. So
far I have in A10 - Type of payment Cash /Card . B10 €“ Pick-up Date MM/DD/YY
.. C10 €“ Pick-up Time HH:MM . D10 €“ Drop-off Date MM/DD/YY . E10 €“ Drop-off
Time HH:MM . F10 €“ Overtime Drop-off Time HH:MM . G10 €“ Hour charge $100.00 .
H10 €“ Gratuity 25% . I10 €“ Tax 10% . J10 €“ Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
.. K10 €“ Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 €“
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 €“ Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 €“ Total =IF(K10="","",(SUM(K10:M10)))
O10 €“ Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

I would like to recommend a great book.
Excel Professional Development
The difinitive guide to developing applications using Excel
The project is a Time Clock and the development of the
time clock from code. Check it out at the Library first, just in case
it doesnt have what you need, or what your looking for.
--
William<"M"




"Zsolt Szabó" wrote:

Hi Everybody!
Im a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If Im over the first
10 minutes I have to collect a full hour. I need help to calculate this. So
far I have in A10 - Type of payment Cash /Card . B10 €“ Pick-up Date MM/DD/YY
. C10 €“ Pick-up Time HH:MM . D10 €“ Drop-off Date MM/DD/YY . E10 €“ Drop-off
Time HH:MM . F10 €“ Overtime Drop-off Time HH:MM . G10 €“ Hour charge $100.00 .
H10 €“ Gratuity 25% . I10 €“ Tax 10% . J10 €“ Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 €“ Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 €“
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 €“ Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 €“ Total =IF(K10="","",(SUM(K10:M10)))
O10 €“ Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

correction Book Name "Professional Excel Development"
--
William<"M"




"Zsolt Szabó" wrote:

Hi Everybody!
Im a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If Im over the first
10 minutes I have to collect a full hour. I need help to calculate this. So
far I have in A10 - Type of payment Cash /Card . B10 €“ Pick-up Date MM/DD/YY
. C10 €“ Pick-up Time HH:MM . D10 €“ Drop-off Date MM/DD/YY . E10 €“ Drop-off
Time HH:MM . F10 €“ Overtime Drop-off Time HH:MM . G10 €“ Hour charge $100.00 .
H10 €“ Gratuity 25% . I10 €“ Tax 10% . J10 €“ Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 €“ Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 €“
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 €“ Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 €“ Total =IF(K10="","",(SUM(K10:M10)))
O10 €“ Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

Hi Zsolt.
See if this file is any help to you.
http://savefile.com/files/1023431
When you get to the site, you'll see a download button near the bottom of
the screen.
After you download and open the file: You type in the stuff in the blue
boxes. The rest is calculated. I put in some sample info to test it. You
can replace this with your own data.
HTH, James

"Zsolt Szabó" wrote in message
...
Hi Everybody!
I'm a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If I'm over the
first
10 minutes I have to collect a full hour. I need help to calculate this.
So
far I have in A10 - Type of payment Cash /Card . B10 - Pick-up Date
MM/DD/YY
. C10 - Pick-up Time HH:MM . D10 - Drop-off Date MM/DD/YY . E10 - Drop-off
Time HH:MM . F10 - Overtime Drop-off Time HH:MM . G10 - Hour charge
$100.00 .
H10 - Gratuity 25% . I10 - Tax 10% . J10 - Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 - Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 -
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 - Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 - Total =IF(K10="","",(SUM(K10:M10)))
O10 - Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

Hi Zone,
Thanks a lot for your spreadsheet! I like to be more specific. For example:
when a customer orders a limo they are telling me they need one from 21:00 to
01:00 but they spend more time and we arrived back 02:18. They needed the
limo for 4 hours but they used for 5 hours 18 minutes. The overtime rule is:
every hour has to be charged if we start the hour and are over the first 10
minutes. Every job takes different time, but overtime is considered to be
over the time limit that they have originally asked for.

--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zone" wrote:

Hi Zsolt.
See if this file is any help to you.
http://savefile.com/files/1023431
When you get to the site, you'll see a download button near the bottom of
the screen.
After you download and open the file: You type in the stuff in the blue
boxes. The rest is calculated. I put in some sample info to test it. You
can replace this with your own data.
HTH, James

"Zsolt Szabó" wrote in message
...
Hi Everybody!
I'm a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If I'm over the
first
10 minutes I have to collect a full hour. I need help to calculate this.
So
far I have in A10 - Type of payment Cash /Card . B10 - Pick-up Date
MM/DD/YY
. C10 - Pick-up Time HH:MM . D10 - Drop-off Date MM/DD/YY . E10 - Drop-off
Time HH:MM . F10 - Overtime Drop-off Time HH:MM . G10 - Hour charge
$100.00 .
H10 - Gratuity 25% . I10 - Tax 10% . J10 - Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 - Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 -
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 - Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 - Total =IF(K10="","",(SUM(K10:M10)))
O10 - Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

Taking your example where "overtime" is 1 hour 18 minutes:

Time to be charged (hours) =Int(A1*24)+(MINUTE(A1=11)

where A1=01:18 (hh:m format)

HTH

"Zsolt Szabó" wrote:

Hi Zone,
Thanks a lot for your spreadsheet! I like to be more specific. For example:
when a customer orders a limo they are telling me they need one from 21:00 to
01:00 but they spend more time and we arrived back 02:18. They needed the
limo for 4 hours but they used for 5 hours 18 minutes. The overtime rule is:
every hour has to be charged if we start the hour and are over the first 10
minutes. Every job takes different time, but overtime is considered to be
over the time limit that they have originally asked for.

--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zone" wrote:

Hi Zsolt.
See if this file is any help to you.
http://savefile.com/files/1023431
When you get to the site, you'll see a download button near the bottom of
the screen.
After you download and open the file: You type in the stuff in the blue
boxes. The rest is calculated. I put in some sample info to test it. You
can replace this with your own data.
HTH, James

"Zsolt Szabó" wrote in message
...
Hi Everybody!
I'm a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If I'm over the
first
10 minutes I have to collect a full hour. I need help to calculate this.
So
far I have in A10 - Type of payment Cash /Card . B10 - Pick-up Date
MM/DD/YY
. C10 - Pick-up Time HH:MM . D10 - Drop-off Date MM/DD/YY . E10 - Drop-off
Time HH:MM . F10 - Overtime Drop-off Time HH:MM . G10 - Hour charge
$100.00 .
H10 - Gratuity 25% . I10 - Tax 10% . J10 - Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 - Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 -
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 - Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 - Total =IF(K10="","",(SUM(K10:M10)))
O10 - Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

Hi Toppers, I please take a look at the original spreadsheet
http://www.savefile.com/files/1023934
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Toppers" wrote:

Taking your example where "overtime" is 1 hour 18 minutes:

Time to be charged (hours) =Int(A1*24)+(MINUTE(A1=11)

where A1=01:18 (hh:m format)

HTH

"Zsolt Szabó" wrote:

Hi Zone,
Thanks a lot for your spreadsheet! I like to be more specific. For example:
when a customer orders a limo they are telling me they need one from 21:00 to
01:00 but they spend more time and we arrived back 02:18. They needed the
limo for 4 hours but they used for 5 hours 18 minutes. The overtime rule is:
every hour has to be charged if we start the hour and are over the first 10
minutes. Every job takes different time, but overtime is considered to be
over the time limit that they have originally asked for.

--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zone" wrote:

Hi Zsolt.
See if this file is any help to you.
http://savefile.com/files/1023431
When you get to the site, you'll see a download button near the bottom of
the screen.
After you download and open the file: You type in the stuff in the blue
boxes. The rest is calculated. I put in some sample info to test it. You
can replace this with your own data.
HTH, James

"Zsolt Szabó" wrote in message
...
Hi Everybody!
I'm a limousine chauffeur and I made a small spreadsheet to make the price
calculation faster but I have a problem. I have to collect money for the
service, gratuity, tax and if I have than for overtime. If I'm over the
first
10 minutes I have to collect a full hour. I need help to calculate this.
So
far I have in A10 - Type of payment Cash /Card . B10 - Pick-up Date
MM/DD/YY
. C10 - Pick-up Time HH:MM . D10 - Drop-off Date MM/DD/YY . E10 - Drop-off
Time HH:MM . F10 - Overtime Drop-off Time HH:MM . G10 - Hour charge
$100.00 .
H10 - Gratuity 25% . I10 - Tax 10% . J10 - Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 - Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) . L10 -
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 - Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 - Total =IF(K10="","",(SUM(K10:M10)))
O10 - Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help me.

--
Many thanks for your help in advance. Have a wonderful day and weekend!
Zsolt

I think that Toppers missed a parenthesis:

=Int(A1*24)+(MINUTE(A1)=11)


--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Zsolt Szabó" wrote in message
...
Hi Toppers, I please take a look at the original spreadsheet
http://www.savefile.com/files/1023934
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Toppers" wrote:

Taking your example where "overtime" is 1 hour 18 minutes:

Time to be charged (hours) =Int(A1*24)+(MINUTE(A1=11)

where A1=01:18 (hh:m format)

HTH

"Zsolt Szabó" wrote:

Hi Zone,
Thanks a lot for your spreadsheet! I like to be more specific. For
example:
when a customer orders a limo they are telling me they need one from
21:00 to
01:00 but they spend more time and we arrived back 02:18. They needed
the
limo for 4 hours but they used for 5 hours 18 minutes. The overtime
rule is:
every hour has to be charged if we start the hour and are over the
first 10
minutes. Every job takes different time, but overtime is considered to
be
over the time limit that they have originally asked for.

--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zone" wrote:

Hi Zsolt.
See if this file is any help to you.
http://savefile.com/files/1023431
When you get to the site, you'll see a download button near the
bottom of
the screen.
After you download and open the file: You type in the stuff in the
blue
boxes. The rest is calculated. I put in some sample info to test
it. You
can replace this with your own data.
HTH, James

"Zsolt Szabó" wrote in message
...
Hi Everybody!
I'm a limousine chauffeur and I made a small spreadsheet to make
the price
calculation faster but I have a problem. I have to collect money
for the
service, gratuity, tax and if I have than for overtime. If I'm over
the
first
10 minutes I have to collect a full hour. I need help to calculate
this.
So
far I have in A10 - Type of payment Cash /Card . B10 - Pick-up
Date
MM/DD/YY
. C10 - Pick-up Time HH:MM . D10 - Drop-off Date MM/DD/YY . E10 -
Drop-off
Time HH:MM . F10 - Overtime Drop-off Time HH:MM . G10 - Hour charge
$100.00 .
H10 - Gratuity 25% . I10 - Tax 10% . J10 - Hours
=IF(C10="","",IF(E10="","",IF(F10="",((D10+E10)-(B10+C10)),((((D10+E10)-(B10+C10))+((D10+F10)-(D10+E10)))))))
. K10 - Service Charge =IF(J10="","",(((J10-INT(J10))*24)*G10)) .
L10 -
Gratuity Charge =IF(K10="","",(K10*H10)) . M10 - Tax Charge
=IF(K10="","",((K10+L10)*I10)) N10 - Total
=IF(K10="","",(SUM(K10:M10)))
O10 - Overtime =IF(F10="","",((D10+F10)-(D10+E10))) . Please help
me.

--
Many thanks for your help in advance. Have a wonderful day and
weekend!
Zsolt