Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
Hi, I am trying to make time sheet that would forecast employment cost. Payment criteria is that overtime applies after midnight. Sheet that I've designed has four columns per day IE. start & finish time for AM shift IE. (08:00 - 16:00) and start and finish for PM shift. That could be (17:00 - 23:00), which doesn't create problem so far but when inputting something like (18:00 - 04:00) i get total hours worked. What sort of formula should I use that would separate basic hours from overtime hours automatically IE. (6hrs basic and 4hrs overtime). Any help on this matter would be greatly appreciated :) -- Sasha ------------------------------------------------------------------------ Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608 View this thread: http://www.excelforum.com/showthread...hreadid=563527 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
Hi!
A1 = start time = 18:00 B1 = end time = 4:00 For Reg hrs: =IF(COUNT(A1:B1)<2,"",IF(B1<A1,(1-A1)*24,(B1-A1)*24)) For OT hrs: =IF(COUNT(A1:B1)<2,"",IF(B1<A1,B1*24,0)) Biff "Sasha" wrote in message ... Hi, I am trying to make time sheet that would forecast employment cost. Payment criteria is that overtime applies after midnight. Sheet that I've designed has four columns per day IE. start & finish time for AM shift IE. (08:00 - 16:00) and start and finish for PM shift. That could be (17:00 - 23:00), which doesn't create problem so far but when inputting something like (18:00 - 04:00) i get total hours worked. What sort of formula should I use that would separate basic hours from overtime hours automatically IE. (6hrs basic and 4hrs overtime). Any help on this matter would be greatly appreciated :) -- Sasha ------------------------------------------------------------------------ Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608 View this thread: http://www.excelforum.com/showthread...hreadid=563527 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
Hi
The general formula for time difference calculating with midnight rollovet taken into account =B1-A1+(B1<A1) where A1 contains start time, and B1 contains end time. When allways basic time is up to 6 hours, and all over 6 hours is overtime, then Basic time =MIN(6/24,B1-A1+(B1<A1)) Overtime =MAX(0,B1-A1+(B1<A1)-6/24) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Sasha" wrote in message ... Hi, I am trying to make time sheet that would forecast employment cost. Payment criteria is that overtime applies after midnight. Sheet that I've designed has four columns per day IE. start & finish time for AM shift IE. (08:00 - 16:00) and start and finish for PM shift. That could be (17:00 - 23:00), which doesn't create problem so far but when inputting something like (18:00 - 04:00) i get total hours worked. What sort of formula should I use that would separate basic hours from overtime hours automatically IE. (6hrs basic and 4hrs overtime). Any help on this matter would be greatly appreciated :) -- Sasha ------------------------------------------------------------------------ Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608 View this thread: http://www.excelforum.com/showthread...hreadid=563527 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
Thanks Biff, Formulas work very well, thanks the only time problem occurs is when shift starts at midnight IE (00:00 - 05:00) formula calculates all the time as basic when it should be overtime (any hours done after midnight). Any ideas? Thanks, Sasha -- Sasha ------------------------------------------------------------------------ Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608 View this thread: http://www.excelforum.com/showthread...hreadid=563527 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
"Sasha" wrote....
Thanks Biff, Formulas work very well, thanks the only time problem occurs is when shift starts at midnight IE (00:00 - 05:00) formula calculates all the time as basic when it should be overtime (any hours done after midnight). Well, you didn't mention that shifts can start after midnight in your original post! If Day shift starts at 8:00 AM does that mean any shift start from 12:00 AM to 7:59 AM is overtime? Biff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time difference calculation
Hi guys, Thanks to Arvi problem is solved now :) Formula for basic time is: =IF(B1<A1,1,MAX(8/24,B1))*24-MAX(8/24,A1)*24 And formula for overtime is: =((B1<A1)*B1 + AND(B1A1,A1<8/24)*(MIN(8/24,B1)-A1))*24 Workes well so far ! Thanks to all who tried to help :-) Regards, Sasha -- Sasha ------------------------------------------------------------------------ Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608 View this thread: http://www.excelforum.com/showthread...hreadid=563527 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Time Difference | Excel Discussion (Misc queries) | |||
time sheet drop down lists | Excel Discussion (Misc queries) | |||
Time Calculation For A Timesheet To Include Lunch | Excel Worksheet Functions | |||
perform calculation only if current time is after 10 am | Excel Worksheet Functions | |||
Time zone Time difference | Excel Worksheet Functions |