Solved

how calculate the actual working time ?

Posted on 2014-02-01
8
539 Views
Last Modified: 2014-02-05
Hi,

I have a real data from a database. The difference between two dates give me the real time, wheres, I want the actual working time. for example:

       Time one                                 Time two                             difference  
31/1/2014 16:00:00             03/02/2014 08:00:00                       64 hours

While the actual working time is: one hour (which is from 16.00 to 17.00) and the rest of hours was the weekend.

how can I calculate the actual working time? if I have 9 hours /day in 5 days/ week


Thanks
0
Comment
Question by:obad62
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Accepted Solution

by:
Raheman M. Abdul earned 84 total points
ID: 39826311
Use this sheet
Replace the holiday dates in the list
Hope working times are correct

Formula used to calculate elapsed times is:

=IF(OR(A4="",B4=""),"",(NETWORKDAYS(A4,B4,$F$4:$F$12)-1)*($E$5-$E$4)+IF(OR(ISNUMBER(MATCH(INT(B4),$F$4:$F$12,0)),WEEKDAY(B4,2)>5),$E$5,MEDIAN(MOD(B4,1),$E$5,$E$4))-IF(OR(ISNUMBER(MATCH(INT(A4),$F$4:$F$12,0)),WEEKDAY(A4,2)>5),$E$4,MEDIAN(MOD(A4,1),$E$5,$E$4)))
TimeDifferenceExclHolidays.xlsx
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 39826325
You can use this function:
Public Function ISO_WorkTimeDiff( _
  ByVal datDateTimeFrom As Date, _
  ByVal datDateTimeTo As Date, _
  Optional ByVal booNoHours As Boolean) _
  As Long

' Purpose: Calculate number of working minutes between date/times datDateTimeFrom and datDateTimeTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Returns: "Working minutes". Divide by 60 to obtain working hours.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, Copenhagen
'
' If booNoHours is True, time values are ignored.

  ' Specify begin and end time of daily working hours.
  Const cdatWorkTimeStart   As Date = #8:00:00 AM#
  Const cdatWorkTimeStop    As Date = #5:00:00 PM#
  Const cbytWorkdaysOfWeek  As Byte = 5

  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim datTimeFrom           As Date
  Dim datTimeTo             As Date
  Dim lngDays               As Long
  Dim lngMinutes            As Long
  Dim lngWorkMinutesDaily   As Long
  
  ' No special error handling.
  On Error Resume Next
  
  If DateDiff("n", datDateTimeFrom, datDateTimeTo) <= 0 Then
    ' Nothing to do. Return zero.
  Else
    
    ' Calculate number of daily "working minutes".
    lngWorkMinutesDaily = DateDiff("n", cdatWorkTimeStart, cdatWorkTimeStop)
    
    ' Find ISO weekday for Sunday.
    bytSunday = Weekday(vbSunday, vbMonday)
    
    ' Find weekdays for the dates.
    intWeekdayDateFrom = Weekday(datDateTimeFrom, vbMonday)
    intWeekdayDateTo = Weekday(datDateTimeTo, vbMonday)
    
    ' Compensate weekdays' value for non-working days (weekends).
    intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
    intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
    
    ' Calculate number of working days between the weeks of the two dates.
    lngDays = (cbytWorkdaysOfWeek * DateDiff("w", datDateTimeFrom, datDateTimeTo, vbMonday, vbFirstFourDays))
    ' Add number of working days between the two weekdays, ignoring number of weeks.
    lngDays = lngDays + intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
    
    If Not booNoHours = True Then
      ' Extract begin and stop hour (time) for the working period.
      datTimeFrom = TimeSerial(Hour(datDateTimeFrom), Minute(datDateTimeFrom), Second(datDateTimeFrom))
      datTimeTo = TimeSerial(Hour(datDateTimeTo), Minute(datDateTimeTo), Second(datDateTimeTo))
      ' Adjust times before or after daily working hours to boundaries of working hours.
      If DateDiff("n", datTimeFrom, cdatWorkTimeStart) > 0 Then
        datTimeFrom = cdatWorkTimeStart
      ElseIf DateDiff("n", datTimeFrom, cdatWorkTimeStop) < 0 Then
        datTimeFrom = cdatWorkTimeStop
      End If
      If DateDiff("n", datTimeTo, cdatWorkTimeStart) > 0 Then
        datTimeTo = cdatWorkTimeStart
      ElseIf DateDiff("n", datTimeTo, cdatWorkTimeStop) < 0 Then
        datTimeTo = cdatWorkTimeStop
      End If
      
      ' Calculate number of working minutes between the two days, ignoring number of days.
      lngMinutes = DateDiff("n", datTimeFrom, datTimeTo)
    End If
    
    ' Calculate number of working minutes between the two days using the workday count.
    lngMinutes = lngMinutes + (lngDays * lngWorkMinutesDaily)
  
  End If
  
  ISO_WorkTimeDiff = lngMinutes

End Function

Open in new window

The expression:

    ISO_WorkTimeDiff(#1/31/2014 16:00:00#, #02/03/2014 08:00:00#) / 60

returns 1.

/gustav
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 166 total points
ID: 39826814
If you have start time/date in A2 and end time date in B2 and those times will always be within work hours then this formula will be sufficient:

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

Custom format result cell as [h]:mm to get 1:00 for your example.

If you want the answer to be a decimal number then multiply the whole formula by 24 and format result cell as number, i.e.

=24*((NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1))

You can inclued a holiday range in the NETWORKDAYS function if you want to exclude holidays too

If your start and end times might be outside working hours then you can use this longer version of the second formula:

=24*((NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00"))

See attached examples - on the last row I have the end time outside working hours so the first two formulas (marked in red) won't show correct results - the third in green is correct

regards, barry
work-hours.xlsx
0
 

Author Comment

by:obad62
ID: 39828199
Thanks for all.

 They are useful solutions, however, the problem still happen.
these solutions avoid the calculation of weekends hours and out work hours, but if the time between two dates is 24:30 hours for example. the function return 00:30:00 which will be difficult to identify whether the difference between these dates is 30 minutes or 24.30 hours

I am working on thousands of records

Thanks
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 39828294
Your statement is very confusing.

You cannot just state a time difference, you need the start time as well, and your question was not about a given time difference but about the count of workhours betweeen two times which my function returns correctly as to your example data.

If your 24:30 falls within a weekend, count of workhours will be zero.
If starting on a workday, you will recieve some workhours, say:

   ISO_WorkTimeDiff(#02/03/2014 09:00#, #02/04/2014 09:30#) / 60

it will return 9.5 hours.

/gustav
0
 

Author Comment

by:obad62
ID: 39828385
Sorry Gustav, I couldn't understand your previous comment. I am using Excel sheet and i don't know what your comment written by !!! However, I  appreciate your time and effort with me. I need something in Excel
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 166 total points
ID: 39828642
>but if the time between two dates is 24:30 hours for example. the function return 00:30:00

This is a formatting problem - if you format as hh:mm then that format is for "clock times" and is limited to 23:59. To show "elapsed times", possibly 24 hours + then use the custom format

[h]:mm

Using the square brackets means you'll see 24:30 rather than 00:30

regards, barry
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 39828865
But you have tagged the question Access.

Still, I believe you easily can create your own function in VBA in Excel as well.

/gustav
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now