Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how calculate the actual working time ?

Posted on 2014-02-01
8
573 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 19

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 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