#1
|
|||
|
|||
Hours Worked - AM and PM
Hello,
I have a spreadsheet of hours worked by employee labeled Time IN and Time OUT. I need to be able to calculate how many hours were worked in the AM and how many in the PM. My company determines the AM from 4AM to 4PM and the PM from 4PM to 4AM I've tried using if statements and it got way to complicated. I'm wondering if anyone has a solution to my problem. Thanks |
#2
|
||||
|
||||
Without seeing your workbook's structure and some representative content, it's impossible for anyone to give specific advice.
FWIW, though, times are stored as decimal values in Excel. Times before 12 noon are less than 0.5; times after 12 noon are greater than 0.5.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
I echo macropod's comment. Do supply a file to play with.
I recently responded to a similar request here: http://www.excelguru.ca/forums/showt...ll=1#post28751 which could be adapted for your purposes. |
#4
|
|||
|
|||
Without looking at your data (I just set up a case where the hours ran all the way through the clock), I cobbled together a UDF - it requires one column for the count of AM hours and a separate column for the count of PM hours.
The nice thing about this code is that you can change your AM/PM times by simply updating the constants (make sure you divide by 2400 (I use military time :-\ but it works out the same)) Code:
Public Function am_pm(gnrStart As Range, _ gnrEnd As Range, _ ampm As String) As Double Const am = 400 / 2400 ' ~~ AM is considered from 0400 - 1600 Const pm = 1600 / 2400 ' ~~ PM is considered from 1600 - 0400 Dim amShift As Double, _ pmShift As Double, _ timeStart As Double, _ timeEnd As Double timeStart = gnrStart.value If timeStart >= 1 Then timeStart = timeStart - 1 timeEnd = gnrEnd.value If timeEnd >= 1 Then timeEnd = timeEnd - 1 'Debug.Print timeStart * 24 & " | " & timeEnd * 24 Select Case ampm Case "AM" ' ~~ Stop function if both start and end times are within PM range If (IsBetween(timeStart, pm, 1, "excl") = True Or IsBetween(timeStart, 0, am, "excl") = True) And _ (IsBetween(timeEnd, pm, 1, "excl") = True Or IsBetween(timeEnd, 0, am, "excl") = True) Then Exit Function ' ~~ Start time in AM (between 0400 - 1600) ElseIf IsBetween(timeStart, am, pm, "incl") = True Then If timeEnd <= pm Then am_pm = (Abs(timeEnd - timeStart) + (timeEnd < timeStart)) * 24 ' ~~ If start and end times are within AM block || http://www.cpearson.com/excel/overtime.htm ElseIf timeEnd > pm Then am_pm = Abs(pm - timeStart) * 24 ' ~~ If start time within AM and end time is past 1600 End If ' ~~ Start time in PM (1600 - 0400) & end time in AM (0400 - 1600) ElseIf (IsBetween(timeStart, pm, 1, "incl") = True Or _ IsBetween(timeStart, 0, am, "incl") = True) And timeEnd >= am Then am_pm = Abs(timeEnd - am) * 24 ' ~~ If start time within PM and end time is past 0400 End If Case "PM" ' ~~ Stop function if both start and end times are within AM range If IsBetween(timeStart, am, pm, "excl") = True And _ IsBetween(timeEnd, am, pm, "excl") = True Then Exit Function ' ~~ Start time in PM (between 1600 - 2359 and 0000 - 0400) ElseIf IsBetween(timeStart, pm, 1, "excl") = True Or _ IsBetween(timeStart, 0, am, "excl") = True Then ' ~~ End time in PM (before 0400) If timeEnd <= am Then am_pm = (timeEnd + Abs(1 - timeStart)) * 24 ' ~~ If start and end times are within PM block (1600 - 0400) ElseIf timeEnd > am Then If timeStart < am Then ' ~~ If start time within PM and end time within AM (past 0400) am_pm = Abs(am - timeStart) * 24 Else am_pm = (am + Abs(1 - timeStart)) * 24 End If End If ElseIf timeEnd > pm Then am_pm = Abs(timeEnd - pm) * 24 ' ~~ If start time within AM (before 1600) and _ end time within PM (past 1600) ElseIf timeStart < am Then am_pm = Abs(am - timeStart) * 24 ' ~~ If start time within PM (past 1600) and _ end time within AM (past 0400) End If End Select End Function Code:
Function IsBetween(X, _ X1, _ X2, _ Optional InclExcl = "incl") As Boolean ' ~~ Test if ?X? is between two values, X1 and X2 ' http://www.vbaexpress.com/kb/getarticle.php?kb_id=779 '**************************************************************************************** ' Title IsBetween ' Target Application: any ' Function; determines if X is between X1 and X2 ' either X1 or X2 can be the min; the other is the max; ' if X1 = X2 and test is inclusive, IsBetween will be true IFF ' X = X1 = X2 ' if X1 = X2 and test is exclusive, IsBetween is always False ' Passed Values: ' X [in, numeric] ' X1 [in, numeric] one side of test ' X2 [in, numeric] other side of test ' InclExcl [in, string, optioal] Inclusive or Exclusive flag ' '**************************************************************************************** Dim Xmax Dim Xmin ' detmine min and max values If X1 <= X2 Then Xmin = X1 Xmax = X2 Else Xmin = X2 Xmax = X1 End If Select Case LCase(InclExcl) Case "incl" ' test includes both X1 and X2: Xmin <= X <= Xmax If X >= Xmin And X <= Xmax Then IsBetween = True Else IsBetween = False End If Case "excl" ' test excludes both X1 and X2: Xmin < X < Xmax If X > Xmin And X < Xmax Then IsBetween = True Else IsBetween = False End If Case Else MsgBox "bad call to IsBetween" End Select End Function |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to find out hours worked between midnight and 6 am | acorn | Excel | 2 | 10-15-2016 01:45 AM |
Complicated calculation of hours worked | APreston | Excel | 1 | 05-09-2016 04:42 AM |
Very complicated calculation of total hours worked (PLEASE HELP) | APreston | Excel | 1 | 05-08-2016 02:58 AM |
Help with 'hours worked' calculation... | Snvlsfoal | Excel | 1 | 08-11-2011 05:54 AM |
Finding where I last worked on a document. | Balliol | Word | 1 | 11-24-2010 04:50 AM |