If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Business Hours Between 2 Dates
I found some help in another post which brought me a bit closer to the result I am looking for. I need to calculate business hours between 2 dates. I am using a formula that I found on the site which excludes weekends, which is one piece of it, but I also need to know how to modify this formula even more to show a start time and a cutoff time. This is the formula I am using: =NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1) *** This is the dilemma, lets say that the business hours are from 7a-6p, so if something was submitted after 5pm, I wouldn't want the time to be calculated until the beginning of the next working day. ** Is this possible? Ex. 3/27/2006 17:22 - 3/28/2006 8:24 would show 15:02 as the result. But because of the time this was submitted, I would want the time to start from 7a on 3/28/06. So in actuality, I would want the result to be 1:24. -- tanya216 ------------------------------------------------------------------------ tanya216's Profile: http://www.excelforum.com/member.php...o&userid=33149 View this thread: http://www.excelforum.com/showthread...hreadid=529630 |
#2
|
|||
|
|||
Calculating Business Hours Between 2 Dates
Okay, I feel like I'm getting closer. This formula also works, but I haven't been able to pull it all together. I am using the new formula that was submitted, but on some of my calculations the result is not calculating the total time. Here is what I'm using based on the previous post: =NETWORKDAYS(IF(MOD(A2,1)17/24,A2+1,A2),B2)-1-IF(MOD(A2,1)17/24,7/24,MOD(A2,1))+MOD(B2,1) Now this works when the times are in the same day, or if the 2 times are from one day to the next (like Ex. 3/27/2006 17:22 - 3/28/2006 8:24), but what if the times elasp over a 2 day period, or over a weekend or something. How can I make it all accumulative to calculate the total elasped time from start to finish? Same example but modified: Ex. 3/27/2006 14:22 - 3/29/2006 10:24 (Same cutoff times - 5p & 7a) - The formula above is only calculating one of these days, how can I adjust it to calculate all of the hours which should include: - the hours on 3/27/06 from 14:22-17:00 - all hours on 3/28/06 - and the hours on 3/29/06 from 7a-10:24a ** Is this complicated? ** I really appreciate the help on this. Thanking everyone in advance.' Tanya -- tanya216 ------------------------------------------------------------------------ tanya216's Profile: http://www.excelforum.com/member.php...o&userid=33149 View this thread: http://www.excelforum.com/showthread...hreadid=529630 |
#3
|
|||
|
|||
Calculating Business Hours Between 2 Dates
Hi!
Just noticed your post while out searching for something else. I have used a User -Defined Function to do this sort of thing. This is it : Function WorkedHours(Date1 As Double, Date2 As Double) As Double Dim D As Date Dim D1 As Date Dim D2 As Date Dim T As Double Dim T1 As Double Dim T2 As Double Dim Total As Double Dim DayDiff As Double Const HalfHour = 1 / 48 Const DayStart As Double = 8.5 / 24 Const DayEnd As Double = 17 / 24 Const FullDay As Double = DayEnd - DayStart - 2 * HalfHour Const Lunch As Double = 2 * HalfHour D1 = Int(Date1) T1 = Date1 - D1 D2 = Int(Date2) T2 = Date2 - D2 Total = 0 DayDiff = D2 - D1 Select Case DayDiff Case 0 Total = T2 - T1 Case 1 If T1 = 0 And T2 = 0 Then Total = FullDay ElseIf T1 DayEnd And T2 0 Then Total = 1 - T1 + T2 ElseIf T1 DayEnd And T2 0 Then Total = DayEnd - T1 + T2 - DayStart End If If IsFriday(D1) Then Total = Total - HalfHour Case Else For D = D1 To D2 Select Case D Case D1 'start date If T1 = 0 Then Total = Total + FullDay Else: Total = Total + DayEnd - T1 End If If IsFriday(D) Then Total = Total - HalfHour Case D2 'end date If T2 0 Then Total = Total + T2 - DayStart - Lunch If (IsFriday(D) And (DayEnd - T2 - Lunch) 15 * HalfHour) Then Total = Total - HalfHour End If Case Else 'days between If Not (IsSaturday(D) Or IsSunday(D)) Then _ Total = Total + FullDay If IsFriday(D) Then Total = Total - HalfHour End Select Next D End Select WorkedHours = Total * 24 End Function Most of the constants are self-explanatory (remembering that the hours are all being quoted as 1/24 of a day. The situation I use it in has daily hours 8:30 am to 5:00pm (hence 8.5 to 17) but half an hour shorter on Fridays. The "little" functions IsFriday etc follow this pattern and are called as needed. Function IsFriday(Dat As Date) As Boolean IsFriday = False If Weekday(Dat, 1) = vbFriday Then IsFriday = True End Function The 1 inside (Weekday(Dat,1) denotes a week starting on Sunday. Hope this might help. Alf tanya216 wrote: Okay, I feel like I'm getting closer. This formula also works, but I haven't been able to pull it all together. I am using the new formula that was submitted, but on some of my calculations the result is not calculating the total time. Here is what I'm using based on the previous post: =NETWORKDAYS(IF(MOD(A2,1)17/24,A2+1,A2),B2)-1-IF(MOD(A2,1)17/24,7/24,MOD(A2,1))+MOD(B2,1) Now this works when the times are in the same day, or if the 2 times are from one day to the next (like Ex. 3/27/2006 17:22 - 3/28/2006 8:24), but what if the times elasp over a 2 day period, or over a weekend or something. How can I make it all accumulative to calculate the total elasped time from start to finish? Same example but modified: Ex. 3/27/2006 14:22 - 3/29/2006 10:24 (Same cutoff times - 5p & 7a) - The formula above is only calculating one of these days, how can I adjust it to calculate all of the hours which should include: - the hours on 3/27/06 from 14:22-17:00 - all hours on 3/28/06 - and the hours on 3/29/06 from 7a-10:24a ** Is this complicated? ** I really appreciate the help on this. Thanking everyone in advance.' Tanya -- tanya216 ------------------------------------------------------------------------ tanya216's Profile: http://www.excelforum.com/member.php...o&userid=33149 View this thread: http://www.excelforum.com/showthread...hreadid=529630 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to calulate time in hours cbetween two different dates? | shonkoo | Worksheet Functions | 3 | October 30th, 2005 03:30 AM |
Calculating number of days between two dates that fall between two other dates | [email protected] | General Discussion | 5 | October 26th, 2005 06:18 PM |
need help w/formula for calculating overtime hours | jv749297 | Worksheet Functions | 1 | January 17th, 2005 07:54 PM |
Calculating Due Dates | Ajit | Worksheet Functions | 1 | June 10th, 2004 07:24 AM |
Series of dates in one column, hours for each date in another | Jon Peltier | Charts and Charting | 1 | March 11th, 2004 12:59 PM |