|
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
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 |
Overtime Calculation
Sandy Mann please take a look at http://www.savefile.com/files/1024045 and
try to fix the J14 J15 and J16 cells for me please -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Sandy Mann" wrote: 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 |
Overtime Calculation
From your previous postings, I understand the Pick-up and Drop-down
Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
I think formula could be simplified: =IF(C15="","",IF(E15="","",IF(F15="",(D15+E15)-(B15+C15),(D15+F15)-(B15+C15)))) And others could be simplified by removing redundant brackets: e.g column K =IF(J10="","",(J10-INT(J10))*24*G10) What happens if Overtime drop is next day e.g after midnight? "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
Please look at this J10 J11 J12 the only problem.
http://www.savefile.com/files/1024112 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
You right!!! I tried to fix it but I couldn't. Can YOU fix it and upload for
me please? http://www.savefile.com/files/1024150 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: I think formula could be simplified: =IF(C15="","",IF(E15="","",IF(F15="",(D15+E15)-(B15+C15),(D15+F15)-(B15+C15)))) And others could be simplified by removing redundant brackets: e.g column K =IF(J10="","",(J10-INT(J10))*24*G10) What happens if Overtime drop is next day e.g after midnight? "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
You right!!! I tried to fix it but I couldn't. Can YOU fix it and upload for
me please? http://www.savefile.com/files/1024150 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Zsolt SzabĂł" wrote: Please look at this J10 J11 J12 the only problem. http://www.savefile.com/files/1024112 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
try:
http://www.savefile.com/files/1024202 "Zsolt SzabĂł" wrote: You right!!! I tried to fix it but I couldn't. Can YOU fix it and upload for me please? http://www.savefile.com/files/1024150 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Zsolt SzabĂł" wrote: Please look at this J10 J11 J12 the only problem. http://www.savefile.com/files/1024112 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
Dear Toppers! You fixed the wrong version. Please fix this
http://www.savefile.com/files/1024150 Thank you! -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: try: http://www.savefile.com/files/1024202 "Zsolt SzabĂł" wrote: You right!!! I tried to fix it but I couldn't. Can YOU fix it and upload for me please? http://www.savefile.com/files/1024150 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Zsolt SzabĂł" wrote: Please look at this J10 J11 J12 the only problem. http://www.savefile.com/files/1024112 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
Overtime Calculation
http://www.savefile.com/files/1024287
"Zsolt SzabĂł" wrote: Dear Toppers! You fixed the wrong version. Please fix this http://www.savefile.com/files/1024150 Thank you! -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: try: http://www.savefile.com/files/1024202 "Zsolt SzabĂł" wrote: You right!!! I tried to fix it but I couldn't. Can YOU fix it and upload for me please? http://www.savefile.com/files/1024150 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Zsolt SzabĂł" wrote: Please look at this J10 J11 J12 the only problem. http://www.savefile.com/files/1024112 -- Many thanks for your help in advance. Have a wonderful day! Zsolt "Toppers" wrote: From your previous postings, I understand the Pick-up and Drop-down Date/times are the original "planned" hire times and the Overtime Data/Time is the actual drop time which you use to calculate the overtime hours. If this is the case, it is not reflected in the sample worksheet whch doesn't have a Overtime date/Time column. You need to be clear about the "planned" hire times vs. the "actual" hire times. P.s. Thanks to Sandy for picking up the typo in my earlier posting. "Sandy Mann" wrote: 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 |
| All times are GMT +1. The time now is 12:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com