|
calculate hours using start time & end time, excluding weekends
Hi,
i am trying to calculate using VBA, the no of shift hours worked per day. each day hours =9 hrs. shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a day) e.g: === I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00 PM than it will be considered as 1 Day 1 hour job thursday - 6:00 PM-5:00 PM=1hr friday - 6:00 PM-9:00 AM=9 hrs total=10 hrs or 1 Day 1 hour Things to be considered while writing the code: 1 Day = (9 AM to 6:00 PM) - that's our shift timing. *** Weekends should be excluded (for ex: if we got some work on Friday 5:00 PM) and we finishes the work on Monday 01:00PM) than this should be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM) + Monday 4 hours (1:00 PM - 9:00 AM) The total productive hours for each day should lie between 9:00 am-6:00 pm, excluding weekends(saturday & sunday). i am able to calculate the no of days worked excluding weekends using a do while & select case using weekday function like this: Module1 Code ===================== Function TurnaroundTime(startdate As Date, enddate As Date, stime As Date, etime As Date) On Error GoTo dt_err Dim days, hr Dim starttime, endtime Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start") Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end") days = 0 hr = 0 Do While startdate <= enddate Select Case Weekday(startdate) Case Is = 1, 7 days = days ' hr = hr Case Is = 2, 3, 4, 5, 6 days = days + 1 ' hr= 'Need something here to calculate the time diff properly as per the 'day change. Case Else dt_err: MsgBox "Error#: " _ & Err.Number _ & vbCrLf _ & "Description: " _ & Err.Description Resume exit_func End Select startdate = startdate + 1 Loop TurnaroundTime = days exit_func: Exit Function End Function =================== Sheet1("SLA Hrs") code ================== Private Sub Worksheet_Activate() Dim dx As Integer Dim Cell Dim r, lrow With ActiveSheet r = 4 lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row ' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select ' MsgBox lrow - r + 1 For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1)) Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4)) .Cells(r, 5).Value = Cell .Cells(r, 5).Activate r = r + 1 Next Cell End With End Sub ======================= I tried a lot of permutations & combinations using IFs but cannot get the correct answer... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards. |
calculate hours using start time & end time, excluding weekends
Please note that the Startdate & Enddate are not entered in Date-Time
format and have their columns. starttime & endtime also have their own seperate columns. On Apr 7, 12:58 am, "noname" wrote: Hi, i am trying to calculate using VBA, the no of shift hours worked per day. each day hours =9 hrs. shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a day) e.g: === I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00 PM than it will be considered as 1 Day 1 hour job thursday - 6:00 PM-5:00 PM=1hr friday - 6:00 PM-9:00 AM=9 hrs total=10 hrs or 1 Day 1 hour Things to be considered while writing the code: 1 Day = (9 AM to 6:00 PM) - that's our shift timing. *** Weekends should be excluded (for ex: if we got some work on Friday 5:00 PM) and we finishes the work on Monday 01:00PM) than this should be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM) + Monday 4 hours (1:00 PM - 9:00 AM) The total productive hours for each day should lie between 9:00 am-6:00 pm, excluding weekends(saturday & sunday). i am able to calculate the no of days worked excluding weekends using a do while & select case using weekday function like this: Module1 Code ===================== Function TurnaroundTime(startdate As Date, enddate As Date, stime As Date, etime As Date) On Error GoTo dt_err Dim days, hr Dim starttime, endtime Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start") Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end") days = 0 hr = 0 Do While startdate <= enddate Select Case Weekday(startdate) Case Is = 1, 7 days = days ' hr = hr Case Is = 2, 3, 4, 5, 6 days = days + 1 ' hr= 'Need something here to calculate the time diff properly as per the 'day change. Case Else dt_err: MsgBox "Error#: " _ & Err.Number _ & vbCrLf _ & "Description: " _ & Err.Description Resume exit_func End Select startdate = startdate + 1 Loop TurnaroundTime = days exit_func: Exit Function End Function =================== Sheet1("SLA Hrs") code ================== Private Sub Worksheet_Activate() Dim dx As Integer Dim Cell Dim r, lrow With ActiveSheet r = 4 lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row ' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select ' MsgBox lrow - r + 1 For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1)) Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4)) .Cells(r, 5).Value = Cell .Cells(r, 5).Activate r = r + 1 Next Cell End With End Sub ======================= I tried a lot of permutations & combinations using IFs but cannot get the correct answer... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards. |
calculate hours using start time & end time, excluding weekends
Hi,
Can anyone help me with this please? regards, On Apr 7, 5:24 am, "noname" wrote: Please note that the Startdate & Enddate are not entered in Date-Time format and have their columns. starttime & endtime also have their own seperate columns. On Apr 7, 12:58 am, "noname" wrote: Hi, i am trying to calculate using VBA, the no of shift hours worked per day. each day hours =9 hrs. shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a day) e.g: === I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00 PM than it will be considered as 1 Day 1 hour job thursday - 6:00 PM-5:00 PM=1hr friday - 6:00 PM-9:00 AM=9 hrs total=10 hrs or 1 Day 1 hour Things to be considered while writing the code: 1 Day = (9 AM to 6:00 PM) - that's our shift timing. *** Weekends should be excluded (for ex: if we got some work on Friday 5:00 PM) and we finishes the work on Monday 01:00PM) than this should be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM) + Monday 4 hours (1:00 PM - 9:00 AM) The total productive hours for each day should lie between 9:00 am-6:00 pm, excluding weekends(saturday & sunday). i am able to calculate the no of days worked excluding weekends using a do while & select case using weekday function like this: Module1 Code ===================== Function TurnaroundTime(startdate As Date, enddate As Date, stime As Date, etime As Date) On Error GoTo dt_err Dim days, hr Dim starttime, endtime Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start") Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end") days = 0 hr = 0 Do While startdate <= enddate Select Case Weekday(startdate) Case Is = 1, 7 days = days ' hr = hr Case Is = 2, 3, 4, 5, 6 days = days + 1 ' hr= 'Need something here to calculate the time diff properly as per the 'day change. Case Else dt_err: MsgBox "Error#: " _ & Err.Number _ & vbCrLf _ & "Description: " _ & Err.Description Resume exit_func End Select startdate = startdate + 1 Loop TurnaroundTime = days exit_func: Exit Function End Function =================== Sheet1("SLA Hrs") code ================== Private Sub Worksheet_Activate() Dim dx As Integer Dim Cell Dim r, lrow With ActiveSheet r = 4 lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row ' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select ' MsgBox lrow - r + 1 For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1)) Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4)) .Cells(r, 5).Value = Cell .Cells(r, 5).Activate r = r + 1 Next Cell End With End Sub ======================= I tried a lot of permutations & combinations using IFs but cannot get the correct answer... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards. |
calculate hours using start time & end time, excluding weekends
Yes, I can help you with this, but at this moment the PC with the
information has lost Internet. As I recall, calculate the hours for the remainder of the first day, add the number of whole days times 9, and add the number of hours of the last day. Then loop from start day +1 to end day -1, subtracting 9 hours for each day Weekday function returns vbSaturday or vbSunday. When I get the other PC up, I will send you a code snippet to do it. Carl. On Apr 7, 9:19 am, "noname" wrote: Hi, Can anyone help me with this please? regards, On Apr 7, 5:24 am, "noname" wrote: Please note that the Startdate & Enddate are not entered in Date-Time format and have their columns. starttime & endtime also have their own seperate columns. On Apr 7, 12:58 am, "noname" wrote: Hi, i am trying to calculate using VBA, the no of shift hours worked per day. each day hours =9 hrs. shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a day) e.g: === I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00 PM than it will be considered as 1 Day 1 hour job thursday - 6:00 PM-5:00 PM=1hr friday - 6:00 PM-9:00 AM=9 hrs total=10 hrs or 1 Day 1 hour Things to be considered while writing the code: 1 Day = (9 AM to 6:00 PM) - that's our shift timing. *** Weekends should be excluded (for ex: if we got some work on Friday 5:00 PM) and we finishes the work on Monday 01:00PM) than this should be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM) + Monday 4 hours (1:00 PM - 9:00 AM) The total productive hours for each day should lie between 9:00 am-6:00 pm, excluding weekends(saturday & sunday). i am able to calculate the no of days worked excluding weekends using a do while & select case using weekday function like this: Module1 Code ===================== Function TurnaroundTime(startdate As Date, enddate As Date, stime As Date, etime As Date) On Error GoTo dt_err Dim days, hr Dim starttime, endtime Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start") Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end") days = 0 hr = 0 Do While startdate <= enddate Select Case Weekday(startdate) Case Is = 1, 7 days = days ' hr = hr Case Is = 2, 3, 4, 5, 6 days = days + 1 ' hr= 'Need something here to calculate the time diff properly as per the 'day change. Case Else dt_err: MsgBox "Error#: " _ & Err.Number _ & vbCrLf _ & "Description: " _ & Err.Description Resume exit_func End Select startdate = startdate + 1 Loop TurnaroundTime = days exit_func: Exit Function End Function =================== Sheet1("SLA Hrs") code ================== Private Sub Worksheet_Activate() Dim dx As Integer Dim Cell Dim r, lrow With ActiveSheet r = 4 lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row ' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select ' MsgBox lrow - r + 1 For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1)) Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4)) .Cells(r, 5).Value = Cell .Cells(r, 5).Activate r = r + 1 Next Cell End With End Sub ======================= I tried a lot of permutations & combinations using IFs but cannot get the correct answer... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards. |
calculate hours using start time & end time, excluding weekends
Hi Carl,
Thanks for your reply. As you can see running my code, i get the days correct(actually its a very simple code), but i am not able to get the algorithm correctly to calculate the total hours & show them as Days-Hours. Hope i can get some assistance on above code from you.. Cheers! On Apr 9, 8:38 am, "Carl Hartness" wrote: Yes, I can help you with this, but at this moment the PC with the information has lost Internet. As I recall, calculate the hours for the remainder of the first day, add the number of whole days times 9, and add the number of hours of the last day. Then loop from start day +1 to end day -1, subtracting 9 hours for each day Weekday function returns vbSaturday or vbSunday. When I get the other PC up, I will send you a code snippet to do it. Carl. On Apr 7, 9:19 am, "noname" wrote: Hi, Can anyone help me with this please? regards, On Apr 7, 5:24 am, "noname" wrote: Please note that the Startdate & Enddate are not entered in Date-Time format and have their columns. starttime & endtime also have their own seperate columns. On Apr 7, 12:58 am, "noname" wrote: Hi, i am trying to calculate using VBA, the no of shift hours worked per day. each day hours =9 hrs. shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a day) e.g: === I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00 PM than it will be considered as 1 Day 1 hour job thursday - 6:00 PM-5:00 PM=1hr friday - 6:00 PM-9:00 AM=9 hrs total=10 hrs or 1 Day 1 hour Things to be considered while writing the code: 1 Day = (9 AM to 6:00 PM) - that's our shift timing. *** Weekends should be excluded (for ex: if we got some work on Friday 5:00 PM) and we finishes the work on Monday 01:00PM) than this should be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM) + Monday 4 hours (1:00 PM - 9:00 AM) The total productive hours for each day should lie between 9:00 am-6:00 pm, excluding weekends(saturday & sunday). i am able to calculate the no of days worked excluding weekends using a do while & select case using weekday function like this: Module1 Code ===================== Function TurnaroundTime(startdate As Date, enddate As Date, stime As Date, etime As Date) On Error GoTo dt_err Dim days, hr Dim starttime, endtime Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start") Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end") days = 0 hr = 0 Do While startdate <= enddate Select Case Weekday(startdate) Case Is = 1, 7 days = days ' hr = hr Case Is = 2, 3, 4, 5, 6 days = days + 1 ' hr= 'Need something here to calculate the time diff properly as per the 'day change. Case Else dt_err: MsgBox "Error#: " _ & Err.Number _ & vbCrLf _ & "Description: " _ & Err.Description Resume exit_func End Select startdate = startdate + 1 Loop TurnaroundTime = days exit_func: Exit Function End Function =================== Sheet1("SLA Hrs") code ================== Private Sub Worksheet_Activate() Dim dx As Integer Dim Cell Dim r, lrow With ActiveSheet r = 4 lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row ' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select ' MsgBox lrow - r + 1 For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1)) Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4)) .Cells(r, 5).Value = Cell .Cells(r, 5).Activate r = r + 1 Next Cell End With End Sub ======================= I tried a lot of permutations & combinations using IFs but cannot get the correct answer... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards.- Hide quoted text - - Show quoted text - |
calculate hours using start time & end time, excluding weekends
What do you intend to do with starts and ends outside the normal
shift? 3/31 8 am to 7 pm is 11 hours 4/1 12 am to 4 pm is 16 hours 4/2 2 pm to 9 pm is 7 hours 4/5 2 am to 6 pm is 16 hours Carl On Apr 6, 2:58 pm, "noname" wrote: .... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards. |
calculate hours using start time & end time, excluding weekends
Also, 3/31, 4/1, and 4/7 are weekend days. It's hard to exclude
weekends if start or end is a weekend day. Is your example table bogus? On Apr 10, 7:41 am, "Carl Hartness" wrote: What do you intend to do with starts and ends outside the normal shift? 3/31 8 am to 7 pm is 11 hours 4/1 12 am to 4 pm is 16 hours 4/2 2 pm to 9 pm is 7 hours 4/5 2 am to 6 pm is 16 hours Carl On Apr 6, 2:58 pm, "noname" wrote: ... Sheet Data Dump ============== Start Date End Date Start Time End Time Days Hours Total 3/31/2007 4/7/2007 8:00 AM 7:00 PM 4/1/2007 4/1/2007 12:00 AM 4:00 PM 4/2/2007 4/2/2007 2:00 PM 9:00 PM 4/3/2007 4/3/2007 9:00 AM 6:00 PM 4/4/2007 4/9/2007 2:00 PM 10:00 AM 4/5/2007 4/5/2007 2:00 AM 6:00 PM 4/6/2007 4/7/2007 2:00 PM 10:00 AM Anyone knows how to sort this out? Regards.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 04:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com