Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Timesheet problem
I use an Excel 2000 spreadsheet to log sub-contract work hours - and
as the hours are then used to calculate charges I use separate hours and minutes columns (start hh, start mm; finish hh, finish mm). That seemed the easiest way to me. Everything is working just how I want it, but....I now need to introduce a night charge: A shift can start anytime day/night and finish anytime day/night. If, the shift includes ANY time worked between 1 am and 4 am I then need to (a) invoke an extra charge and (b) do some other checks in a different spreadsheet. How do I get Excel to test for this? Thanks for your help. |
#2
|
|||
|
|||
Timesheet problem
Bit difficult without knowing what the data looks like, but making some
assumptions If A1 holds the start time B1 holds the finish time The time worked between 02:00 and 04:00 can be calculated with =IF(A1<TIME(4,0,0),MIN(B1,TIME(4,0,0)),0)-IF(A1<=TIME(4,0,0),MAX(A1,TIME(1,0 ,0)),0) This will not work if the start and end times are in different days. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Oi you" wrote in message oups.com... I use an Excel 2000 spreadsheet to log sub-contract work hours - and as the hours are then used to calculate charges I use separate hours and minutes columns (start hh, start mm; finish hh, finish mm). That seemed the easiest way to me. Everything is working just how I want it, but....I now need to introduce a night charge: A shift can start anytime day/night and finish anytime day/night. If, the shift includes ANY time worked between 1 am and 4 am I then need to (a) invoke an extra charge and (b) do some other checks in a different spreadsheet. How do I get Excel to test for this? Thanks for your help. |
#3
|
|||
|
|||
Timesheet problem
This function returns day and night work hours even if end time is in the
next date (the maximum difference between end time and start time is 24 hours). Public Function Shifttime2(starttime As Date, endtime As Date, daytime As Integer, _ startday As Date, startnight As Date) Dim length As Variant Dim timeday As Date, timenight As Date If starttime = endtime Then endtime = 1 + endtime If startday = startnight Then startnight = 1 + startnight length = endtime - starttime timeday = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0, endtime - startnight)) _ + WorksheetFunction.Max(0, endtime - (1 + startday)) _ - WorksheetFunction.Max(0, endtime - (1 + startnight)) timenight = WorksheetFunction.Max(0, length - WorksheetFunction.Max(0, startnight - starttime)) _ - WorksheetFunction.Max(0, endtime - (1 + startday)) _ + WorksheetFunction.Max(0, endtime - (1 + startnight)) Select Case daytime Case 1 Shifttime2 = timeday Case 2 Shifttime2 = timenight Case Else Shifttime2 = "Invalid daytime!" End Select End Function Regards, Stefi "Oi you" wrote: I use an Excel 2000 spreadsheet to log sub-contract work hours - and as the hours are then used to calculate charges I use separate hours and minutes columns (start hh, start mm; finish hh, finish mm). That seemed the easiest way to me. Everything is working just how I want it, but....I now need to introduce a night charge: A shift can start anytime day/night and finish anytime day/night. If, the shift includes ANY time worked between 1 am and 4 am I then need to (a) invoke an extra charge and (b) do some other checks in a different spreadsheet. How do I get Excel to test for this? Thanks for your help. |
#4
|
|||
|
|||
Timesheet problem
Thanks everyone for your help. The answer I am now using (courtesy of
Mrexcel.com) is very elegant and works in all circumstances I have tried. Thought you might be interested in it too: =IF(OR(AND(startHH=1,startHH<4), AND(finishHH=1,finishHH<=4), AND(finishHH=4,finishHH-totalhours<4)),"Yes","") This works for finish hours in 24 hour format but it was quite simple to adjust to suit my spreadsheet. It returns "Yes" if any hours were worked between 1am and 4 am and I have been able to take it from there. Buggered if I know how it works but it does! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
overtime on timesheet | Excel Worksheet Functions | |||
timesheet with running total of overtime | Excel Worksheet Functions | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Timesheet Problem | Excel Discussion (Misc queries) |