New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Workday and holiday overtime sheet with alot of conditions

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Workday and holiday overtime sheet with alot of conditions

Postby joefebi » Sun Dec 11, 2011 9:55 pm

Dear experts,

Need your help for working on exel sheet with condition as follows:

Work start 7:00 AM - 17:00 AM
Lunch time 12:00 AM - 13:00 AM
Regular work hours 9:00 AM

OT formula
1. Working day :
1st hour OT = Basic Salary/173*1.5*Variable OT hour
2nd> hour OT = Basic Salary/173*2*VOTH

2. Holiday :
1st>7th hour OT = Basic Salary/173*2*VOTH
8th hour OT = Basic Salary/173*3*VOTH
9th> hour OT = Basic Salary/173*4*VOTH

I'm confuse about :
1.function/formula for total overtime related to 1st hour OT
2.function/formula for total overtime which more than 1 hour
3.function/formula for total overtime related to 2nd> hours OT
3.function/formula for Holiday OT

please help me guys GBU :D

BR//

Joe
  • 0

joefebi
Rookie
 
Posts: 6
Joined: Dec 11, 2011
Reputation: 0

Re: Workday and holiday overtime sheet with alot of conditio

Postby Sisyphus » Mon Dec 12, 2011 4:26 am

Hi Joe,

Is this what you had in mind?
Code: Select all
=ROUND((BS/DIV*MULTI1*IF(VOTH>1,1,VOTH))+(BS/DIV*MULTI2*(IF(VOTH>=1,VOTH-1,0))),2)

BS = Basic Salary
DIV = your divisor of 173
Multi1 = 1.5
Multi2 = 2.0
VOTH = the number of overtime hours worked
I tested this with named cells but you can substitute A1 cell reference which will probably suit you better for at least some of the cases.

You should also be able to adapt the formula to give the results for holiday overtime.

Let me know if there is anything else.
Have a pleasant day!
:D
  • 0

Last edited by Sisyphus on Mon Dec 12, 2011 7:50 am, edited 1 time in total.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Workday and holiday overtime sheet with alot of conditio

Postby joefebi » Mon Dec 12, 2011 7:36 am

Hello Sisyphus,

Thanks for your reply,
I'm a little bit confused for applying to my excelsheet form bellow (example):

A1 : date
B1 : Total Overtime
C1 : Working Day 1st hour
D1 : Working Day 2nd>more hours
H1 : Holiday 1st - 7th hours
I1 : Holiday 8th hours
J1 : Holiday 9th>more hours

please help me cause I'm newbie in excel... GBU :D

Best regards//
  • 0

joefebi
Rookie
 
Posts: 6
Joined: Dec 11, 2011
Reputation: 0

Re: Workday and holiday overtime sheet with alot of conditio

Postby Sisyphus » Mon Dec 12, 2011 8:08 am

Hi Joe,

What is it you want to work out? My formula calculates money. So, where in your imaginary sample sheet is the Basic Pay?

What do you have in B1? Hours, right?
What is in C1 and D1? Is that also hours? Or do you want the result there?
Note that my formula needs one number of hours and calculates one amount of money. So, it doesn't need the first hour to be separated out, nor can it supply two results. If you need to keep these items separate the formulas should be changed. They will be simpler.

Where would you like the result to go? I already asked if you want results in C1 and D1. If not, it doesn't really matter. But I need to know how many amounts you want to work out.

It isn't good practice to integrate static variables invisibly into formulas. The worksheet should show what was calculated. You have 3 static variables in your calculation: 173, 1.5 and 2. You may like to put these three items in some cells at the top of your sheet, with a description in the neighbouring cell, such as "Factor for 1st hour of daily OT", "Factor for 2nd and more hours of daily overtime" etc. The formulas would then refer to the cells where you keep the values and, in future, if there is achange you just change the cell value and continue using the same formulas.

Let us deal with the holidays after we have done the normal days. OK.
Have a great day!
:D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Workday and holiday overtime sheet with alot of conditio

Postby joefebi » Mon Dec 12, 2011 9:08 am

Dear Sisyprus,

Thanks again and yes i need C1 and D1 result for total amount of money.
and maybe it's better if I'll asking in example below ( it's better for my self ) :

Month Salary : 1.000.000
A1 : 13 Dec 11
B1 : day OT hours 2:30
C1 : OT for 1st hours ( 1:00 ) US$ ?
D1 : OT fot 2nd>more hours ( 1:30 ) US$ ??

So sory for my fussiness :D

BR//
  • 0

joefebi
Rookie
 
Posts: 6
Joined: Dec 11, 2011
Reputation: 0

Re: Workday and holiday overtime sheet with alot of conditio

Postby Sisyphus » Mon Dec 12, 2011 9:09 am

Hi Joe,

See if the attachment helps. :D
Regards,
  • 0

You do not have the required permissions to view the files attached to this post.
Last edited by Sisyphus on Sat Jan 07, 2012 10:05 pm, edited 1 time in total.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Workday and holiday overtime sheet with alot of conditio

Postby joefebi » Mon Dec 12, 2011 9:24 am

Dear Sisyprus,

THIS IS ALL COOL !!! THANK YOU SO MUCH MATE...
GOD BLESS YOU :D

Best Regards/
  • 0

joefebi
Rookie
 
Posts: 6
Joined: Dec 11, 2011
Reputation: 0


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 47 guests