Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference between 2 dates, incl weekends, with variable work days
(I posted this in WS Fxns on July 7 & didn't get any response, so I'm hoping to have better luck in this forum, as I really need an answer! Thank you!!) I am creating a problem report spreadsheet for work. In col C I have a date/time that the problem started, eg, 23-Jun-2006 15:20 and in col D I have a date/time for when the problem was resolved, eg, 26-Jun-2006 15:40. I need to calculate the amount of hours that we were not available for use (due to the problem). This is based on a list of available hours. Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs, and Sun 0 hrs. If I do a straight subtraction, it uses 24 hrs for each day - not taking into account our available hrs. If I do NETWORKDAYS it automatically doesn't consider Sat or Sun - and i need it to - and it does have a DayEnd and DayStart but it assumes they are constants. I found the day corresponding to the date and thought I could determine the series of days between it, e.g., if the start day was Friday & the end day Monday, the series in between is Sat & Sun. Then I could do a lookup on all 4 days in a table & add them all together. But I can't figure out how to do this! Can anyone please PLEASE help!? Thanks so much! -- babryanton ------------------------------------------------------------------------ babryanton's Profile: http://www.excelforum.com/member.php...o&userid=36173 View this thread: http://www.excelforum.com/showthread...hreadid=559933 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference between 2 dates, incl weekends, with variable work days
Try Chip Pearson's site at the following URL:
http://www.cpearson.com/excel/datearith.htm You will have to scroll down through the date functions to the time functions. I verified the URL before sending this post and its in working order... -- Kevin Backmann "babryanton" wrote: (I posted this in WS Fxns on July 7 & didn't get any response, so I'm hoping to have better luck in this forum, as I really need an answer! Thank you!!) I am creating a problem report spreadsheet for work. In col C I have a date/time that the problem started, eg, 23-Jun-2006 15:20 and in col D I have a date/time for when the problem was resolved, eg, 26-Jun-2006 15:40. I need to calculate the amount of hours that we were not available for use (due to the problem). This is based on a list of available hours. Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs, and Sun 0 hrs. If I do a straight subtraction, it uses 24 hrs for each day - not taking into account our available hrs. If I do NETWORKDAYS it automatically doesn't consider Sat or Sun - and i need it to - and it does have a DayEnd and DayStart but it assumes they are constants. I found the day corresponding to the date and thought I could determine the series of days between it, e.g., if the start day was Friday & the end day Monday, the series in between is Sat & Sun. Then I could do a lookup on all 4 days in a table & add them all together. But I can't figure out how to do this! Can anyone please PLEASE help!? Thanks so much! -- babryanton ------------------------------------------------------------------------ babryanton's Profile: http://www.excelforum.com/member.php...o&userid=36173 View this thread: http://www.excelforum.com/showthread...hreadid=559933 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference between 2 dates, incl weekends, with variable work days
Hi there - Thank you for replying! Before joining this forum, I did review those chip pearson topics + others on his site. The info was very good, but it still didn't address how to include weekends & how to calculate when I have variable start & end times throughout the week. THus my question here! THanks! -- babryanton ------------------------------------------------------------------------ babryanton's Profile: http://www.excelforum.com/member.php...o&userid=36173 View this thread: http://www.excelforum.com/showthread...hreadid=559933 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference between 2 dates, incl weekends, with variable work days
Hello,
Enter into A1:B7, for example: 04:00 23:30 01:00 23:30 01:00 23:30 01:00 23:30 01:00 23:30 09:00 23:30 00:00 00:00 Then count_hours(C1,D1,A1:B7) will give you the hours from C1 till D1if they are included in table A1:B7 (format resulting cell as dd hh:ss, for example) The UDF count_hours: Function count_hours(dt1 As Date, dt2 As Date, _ vwh As Variant) As Date 'Returns time between dt1 and dt2 but counts only 'hours given in table vwh: for example '04:00 23:30 '01:00 23:30 '01:00 23:30 '01:00 23:30 '01:00 23:30 '09:00 23:30 '00:00 00:00 'This table defines hours to count for each day 'of the week (starting with Monday, 2 columns) 'PB V0.90 Dim dt3 As Date, dt4 As Date, dt5 As Date Dim i As Long If dt2 <= dt1 Then count_hours = 0# Exit Function End If If (Int(dt1) = Int(dt2)) Then dt3 = Int(dt2) + vwh(Weekday(dt2, 2), 2) If dt3 dt2 Then dt3 = dt2 If vwh(Weekday(dt1, 2), 1) = 0 Then dt4 = Int(dt1) Else dt4 = Int(dt1) + vwh(Weekday(dt1, 2), 1) If dt4 < dt1 Then dt4 = dt1 End If count_hours = dt3 - dt4 Exit Function End If If CDbl(dt1) - Int(CDbl(dt1)) = vwh(Weekday(dt1, 2), 2) Then dt3 = 0# Else If vwh(Weekday(dt1, 2), 1) = 0 Then dt3 = Int(dt1) Else dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 1) If dt3 < dt1 Then dt3 = dt1 End If dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 2) - dt3 End If If CDbl(dt2) - Int(CDbl(dt2)) <= vwh(Weekday(dt2, 2), 1) Then dt5 = 0# Else dt5 = Int(dt2) + vwh(Weekday(dt2, 2), 2) If dt5 dt2 Then dt5 = dt2 If vwh(Weekday(dt2, 2), 1) = 0 Then dt5 = dt5 - Int(dt2) Else dt5 = dt5 - Int(dt2) - vwh(Weekday(dt2, 2), 1) End If End If If Int(dt2) - Int(dt1) 1 Then dt4 = 0# For i = Int(dt1) + 1 To Int(dt2) - 1 dt4 = dt4 + vwh(Weekday(i, 2), 2) - vwh(Weekday(i, 2), 1) Next i End If count_hours = dt3 + dt4 + dt5 End Function HTH, Bernd |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference between 2 dates, incl weekends, with variable work days
Thank you very much for your reply! I will look into this and see if it will help me out. Thanks again. -- babryanton ------------------------------------------------------------------------ babryanton's Profile: http://www.excelforum.com/member.php...o&userid=36173 View this thread: http://www.excelforum.com/showthread...hreadid=559933 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
challenging formula(for me), counting days between dates for multipleyears | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Return only work days | Excel Discussion (Misc queries) | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions | |||
Difference between two dates in months with decimals | Excel Worksheet Functions |