calculating difference between date/time fields
I am trying to count the difference between two fields which are date/time to
show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
?B?ZHJldw==?= wrote in
: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! you can use the timevalue function. dim differenceInTime as date with sheets("SheetName") differenceInTime = timevalue(.range("I3").value) - timevalue(.range ("G3").value) end with you would need to alter the code if the it is more than a day i think. hth bst |
calculating difference between date/time fields
http://www.cpearson.com/Excel/DateTimeWS.htm#WorkHours
-- Regards, Tom Ogilvy "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Try
=(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
This looks great.. Is there any way to display the negative numbers?
"Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Hi,
There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
If negative numbers are an issue, simply putting "on time" for any that are
negative would be fine. "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Thsi is a little complicated if you are considering every possible time for
both the start and end time. I recommend a UDF. See the comments in the code below. The cell with the formula may need to be set as text so excel doesn't convert the hours to a number less than 24. If you have 37:00 excel may change this to 12:00 (37 - 24 hours in a day) if the cell is left in general format. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = 24 * FractDays FractMinutes = Hours - Int(Hours) Minutes = Round(60 * FractMinutes, 0) WholeHours = Int(Hours) + (24 * Days) WholeMinutes = Int(Minutes) BusinessHours = _ WholeHours & ":" & Format(WholeMinutes, "#00") End Function "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Will this calculate over 24 hours? I tried to put like 6 days difference in
both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
I simplified my code a little bit but this UDF should give the correct answer.
Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Hi,
Format the formula cell with a custom format of [hh]:mm and you will get the correct answer in hours no matter what the time period. Mike "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Joel,
To make it work if the deal comes in early you need to swap the values so I added these couple of lines at the start of your UDF. If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If Mike "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
I am a little novice when it comes to VBA, but would it simply be
=businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
I added Mike's code to mine. You need to call the formula with two
paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Joel,
I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Joel,
Sorry, I meant to add that while it can be done with a formula your UDF would be far more flexible and is well worth persevering with because I would hate to have to change all the cell reference in that monoltithic thing I created :) Mike "Mike H" wrote: Joel, I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
The problem was easy to fix. The problem occured because the endtime was
earlier in the day then the starttime. If you start at 4:00 PM and end at 9:00 PM the number of days is less than one, but you still have to subtract 15 hours from the difference in the two times. I was only subtracting 15 hours if there was more than one day. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractEndTime As Double Dim FractStartTime As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM Endtime = Int(Endtime) + 1 Endtime = Endtime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM Endtime = Int(Endtime) Endtime = Endtime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) 'If End time of day < Starttime time of day 'subtract an extra 15 hours FractStartTime = StartTime - Int(StartTime) FractEndTime = Endtime - Int(Endtime) If FractEndTime = FractStartTime Then DiffTime = DiffTime - (Days * Time_15H) Else DiffTime = DiffTime - ((Days + 1) * Time_15H) End If 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days 'Hours = Hour(FractDays) + (24 * Days) Hours = Hour(FractDays) + (24 * Days) Minutes = Minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "Mike H" wrote: Joel, Sorry, I meant to add that while it can be done with a formula your UDF would be far more flexible and is well worth persevering with because I would hate to have to change all the cell reference in that monoltithic thing I created :) Mike "Mike H" wrote: Joel, I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. For example Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM Cell I3 is actual closing date 6/23/2008 10:45 AM I would expect to see (1:45) Can anyone help? Thanks! |
calculating difference between date/time fields
Guys - Thank you both for this. This will help a lot!
"Joel" wrote: The problem was easy to fix. The problem occured because the endtime was earlier in the day then the starttime. If you start at 4:00 PM and end at 9:00 PM the number of days is less than one, but you still have to subtract 15 hours from the difference in the two times. I was only subtracting 15 hours if there was more than one day. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractEndTime As Double Dim FractStartTime As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM Endtime = Int(Endtime) + 1 Endtime = Endtime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM Endtime = Int(Endtime) Endtime = Endtime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) 'If End time of day < Starttime time of day 'subtract an extra 15 hours FractStartTime = StartTime - Int(StartTime) FractEndTime = Endtime - Int(Endtime) If FractEndTime = FractStartTime Then DiffTime = DiffTime - (Days * Time_15H) Else DiffTime = DiffTime - ((Days + 1) * Time_15H) End If 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days 'Hours = Hour(FractDays) + (24 * Days) Hours = Hour(FractDays) + (24 * Days) Minutes = Minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "Mike H" wrote: Joel, Sorry, I meant to add that while it can be done with a formula your UDF would be far more flexible and is well worth persevering with because I would hate to have to change all the cell reference in that monoltithic thing I created :) Mike "Mike H" wrote: Joel, I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. |
calculating difference between date/time fields
Joel - This doesn't seem to be taking weekends into account. For example I
have 7/11/2008 4:00 PM 7/14/2008 9:00 AM It should give me 2:00, but it is giving me 18:00. Can you assist? Thanks "Joel" wrote: The problem was easy to fix. The problem occured because the endtime was earlier in the day then the starttime. If you start at 4:00 PM and end at 9:00 PM the number of days is less than one, but you still have to subtract 15 hours from the difference in the two times. I was only subtracting 15 hours if there was more than one day. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractEndTime As Double Dim FractStartTime As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM Endtime = Int(Endtime) + 1 Endtime = Endtime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM Endtime = Int(Endtime) Endtime = Endtime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) 'If End time of day < Starttime time of day 'subtract an extra 15 hours FractStartTime = StartTime - Int(StartTime) FractEndTime = Endtime - Int(Endtime) If FractEndTime = FractStartTime Then DiffTime = DiffTime - (Days * Time_15H) Else DiffTime = DiffTime - ((Days + 1) * Time_15H) End If 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days 'Hours = Hour(FractDays) + (24 * Days) Hours = Hour(FractDays) + (24 * Days) Minutes = Minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "Mike H" wrote: Joel, Sorry, I meant to add that while it can be done with a formula your UDF would be far more flexible and is well worth persevering with because I would hate to have to change all the cell reference in that monoltithic thing I created :) Mike "Mike H" wrote: Joel, I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. |
calculating difference between date/time fields
Joel, do you know how I could not include Saturday and Sunday into this
function? Thanks "Joel" wrote: The problem was easy to fix. The problem occured because the endtime was earlier in the day then the starttime. If you start at 4:00 PM and end at 9:00 PM the number of days is less than one, but you still have to subtract 15 hours from the difference in the two times. I was only subtracting 15 hours if there was more than one day. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractEndTime As Double Dim FractStartTime As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM Endtime = Int(Endtime) + 1 Endtime = Endtime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM Endtime = Int(Endtime) Endtime = Endtime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) 'If End time of day < Starttime time of day 'subtract an extra 15 hours FractStartTime = StartTime - Int(StartTime) FractEndTime = Endtime - Int(Endtime) If FractEndTime = FractStartTime Then DiffTime = DiffTime - (Days * Time_15H) Else DiffTime = DiffTime - ((Days + 1) * Time_15H) End If 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days 'Hours = Hour(FractDays) + (24 * Days) Hours = Hour(FractDays) + (24 * Days) Minutes = Minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "Mike H" wrote: Joel, Sorry, I meant to add that while it can be done with a formula your UDF would be far more flexible and is well worth persevering with because I would hate to have to change all the cell reference in that monoltithic thing I created :) Mike "Mike H" wrote: Joel, I initially thought that the formula could be done with worksheet funcntion It can my formula gives the correct results. I've just done a bit more testing with these 2 values due 1/1/2008 09:00 delivered 31/12/2007 16:00 Your UDF returns 17 hrs when I believe the correct answer is 2 Hrs according to the OP request. Mike "Joel" wrote: I added Mike's code to mine. You need to call the formula with two paramaters like =BusinessHours(A1,A2) , where A1 and A2 are in any date format. It doesn't matter which date format because Excel really stores the date as a number and just displays the number differently depending on the way you format the cell. I initially thought that the formula could be done with worksheet funcntion but I quickly discovered it would be extremely complicated. UDF is easier to debug and add comments so the code can be maintiained. The easiest way of inserting the macro is the following. 1) Press Alt-F11 2) From VBA menu Insert - Module. 3) copy code from posting from the word "Function" to "End Function" 4) Paste into the VBA module window. The function works exactly like any other worksheet function. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double If Endtime < StartTime Then temp = StartTime StartTime = Endtime Endtime = temp End If 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: I am a little novice when it comes to VBA, but would it simply be =businesshours(g3-i3) after pasting the formula? Would I paste your entire wording? Thanks "Joel" wrote: I simplified my code a little bit but this UDF should give the correct answer. Function BusinessHours(StartTime As Date, Endtime As Date) Dim Days As Double Dim DiffTime As Double Dim FractDays As Double Dim FractMinutes As Double Dim Hours As Double Dim Minutes As Double Dim Time_15H As Double Dim WholeHours As Double Dim WholeMinutes As Double 'Move Start time to beginning of next working day If Hour(StartTime) 17 Then 'then add one day and move time to 8:00 AM StartTime = Int(StartTime) + 1 StartTime = StartTime + TimeSerial(8, 0, 0) End If If Hour(StartTime) < 8 Then 'move time to 8:00 AM StartTime = Int(StartTime) StartTime = StartTime + TimeSerial(8, 0, 0) End If 'Move End Time to beginning of next working day If Hour(Endtime) 17 Then 'then add one day and move time to 8:00 AM EndTime = Int(EndTime) + 1 EndTime = EndTime + TimeSerial(8, 0, 0) End If If Hour(Endtime) < 8 Then 'move time to 8:00 AM EndTime = Int(EndTime) EndTime = EndTime + TimeSerial(8, 0, 0) End If DiffTime = Endtime - StartTime 'Now for every day subtract 15 hours (5:00 PM to 8:00 AM) 'days will be integer portion of DiffTime Time_15H = 15 / 24 Days = Int(DiffTime) DiffTime = DiffTime - (Days * Time_15H) 'Create a sdtring with hours and minutes Days = Int(DiffTime) FractDays = DiffTime - Days Hours = hour(FractDays) + (24 * Days) Minutes = minute(FractDays) BusinessHours = _ Hours & ":" & Format(Minutes, "#00") End Function "drew" wrote: Will this calculate over 24 hours? I tried to put like 6 days difference in both cells and it came back with 9 hours. "Mike H" wrote: Hi, There may be a formula to do it but how about this workaround. Use the formula =(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0)) Then for the cell where the formula is apply a conditional format of =G3I3 Apply a colour of (say) green Now if your deal close early it will still show up as a positive number but the cell colour will change to whatever colour you set. Mike "drew" wrote: This looks great.. Is there any way to display the negative numbers? "Mike H" wrote: Try =(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0)) This wouldn't work if a deal closed early so post back if that's an issue. Mike "drew" wrote: I am trying to count the difference between two fields which are date/time to show if we met our service level agreements. However, I don't want to count non business hours. Business Hours are 8-5. |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com