Who's up for a challenge!!

Hello Folks,

I need to create a macro that will be able to determine the total amount of time from 8:30am till 4:30pm between two columns (column A holds the start time and column B holds the close time) and have it insert into column C the total amount of time calculated between two dates and times between the hours of 8:30am and 4:30pm Monday through Friday, not including holidays.
The start date could be something like 16/05/2006 1:30:02 PM and the end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I need the total amount of time there was from 1:30pm till 4:30pm on 16/05/2006 the total amount of time for the next day between 8:30 and 4:30 on 17/05/2006 and the total amount of time on the closing date between 8:30 and 3:30.(3:30 being the time the report was closed).
I only need the times between business hours for each day (which are 8:30am to 4:30pm) from Monday to Friday and have it exclude holidays too.

Example #2: If a report was created after 4:30pm(start time) and not closed until 1:30pm(closed time) the next day, the only time counted would be from 8:30 until 1:30 the following day, because the only time that falls between 8:30 to 4:30 time is on the following day.

Example#2: If a report was created Monday at 11:30am (start time) and not closed until 3:30pm (closed time) Wednesday, then the time counted would be from 11:30am until 4:30pm same day the report was created, Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm because the report was open from Monday to Wednesday and only the time between 8:30 and 4:30 was counted for them days.

A strong feeling of Deja Vue here - did something similar a good few
years back for ICL - with the additional feature that items could be
logged outside of the hours quoted, but wouldn't ACTUALLY be deemed to
start until the working hours (so logged after hours, starts the
following day) AND wouldn't include bank holidays but WOULD include
Saturdays, BUT wouldn't include time that the caller said we couldn't
use (I'm going out, so don't come till tomorrow!) - it was rather a
long formula as I recall Not sure I've got the time to work on it at
the moment though, but you might want to think about the exclusion of
public holidays bit!

Hi, thanks for the reply.

I am not the person or place that had that issue before, but the situation does sound similar to mine.
Do you know if there was anything resolved with that person ?
If so I maybe able to use parts of it to accomplish my own task.

Thanks.

Here is a solution.
Add a reference to VBA Advanced Toolpack

'------------------------
Function WorkTime( _
dStart As Date, _
dEnd As Date, _
hInTime As Date, _
hOutTime As Date, _
rHolidays As Range)

Dim hStart As Date
Dim hEnd As Date
Dim dwStart As Date
Dim dwEnd As Date
Dim lWorkdays As Long
Dim adHolidays() As Date
Dim rCell As Range
Dim iHoliday As Long

'Isolate hours from days
dwStart = Int(dStart)
dwEnd = Int(dEnd)
hStart = dStart - dwStart
hEnd = dEnd - dwEnd

'Resolve Start and End times to Working hours
If hStart < hInTime Then hStart = hInTime
If hEnd > hOutTime Then hEnd = hOutTime

If dwStart = dwEnd Then 'All on same day
'Calculate duration for first and only day
WorkTime = hEnd - hStart
Else 'Calculate duration for first day
If hStart >= hOutTime Then
WorkTime = 0
Else
WorkTime = hOutTime - hStart
End If
'Calculate duration for last day
If hEnd > hInTime Then
WorkTime = WorkTime + (hEnd - hInTime)
End If
End If
'Calculate duration for elapsed whole workdays
ReDim adHolidays(1 To rHolidays.Count)
iHoliday = 1
For Each rCell In rHolidays
adHolidays(iHoliday) = rCell.Value
iHoliday = iHoliday + 1
Next rCell
lWorkdays = networkdays(dwStart, dwEnd, adHolidays)
If lWorkdays >= 3 Then
WorkTime = WorkTime + (lWorkdays - 2) * (hOutTime - hInTime)
End If
End Function
'-------------------------------

"Altec101" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hello Folks,
>
> I need to create a macro that will be able to determine the total
> amount of time from 8:30am till 4:30pm between two columns (column A
> holds the start time and column B holds the close time) and have it
> insert into column C the total amount of time calculated between two
> dates and times between the hours of 8:30am and 4:30pm Monday through
> Friday, not including holidays.
> The start date could be something like 16/05/2006 1:30:02 PM and the
> end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I
> need the total amount of time there was from 1:30pm till 4:30pm on
> 16/05/2006 the total amount of time for the next day between 8:30 and
> 4:30 on 17/05/2006 and the total amount of time on the closing date
> between 8:30 and 3:30.(3:30 being the time the report was closed).
> I only need the times between business hours for each day (which are
> 8:30am to 4:30pm) from Monday to Friday and have it exclude holidays
> too.
>
> Example #2: If a report was created after 4:30pm(start time) and not
> closed until 1:30pm(closed time) the next day, the only time counted
> would be from 8:30 until 1:30 the following day, because the only time
> that falls between 8:30 to 4:30 time is on the following day.
>
> Example#2: If a report was created Monday at 11:30am (start time) and
> not closed until 3:30pm (closed time) Wednesday, then the time counted
> would be from 11:30am until 4:30pm same day the report was created,
> Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm
> because the report was open from Monday to Wednesday and only the time
> between 8:30 and 4:30 was counted for them days.
>
>
> --
> Altec101
> ------------------------------------------------------------------------
> Altec101's Profile:
> http://www.excelforum.com/member.php...o&userid=34539
> View this thread: http://www.excelforum.com/showthread...hreadid=543329
>

Enhanced version (checks wheter start/end date is a workday, simplified
holidays argument processing)

HTH
--
AP

'--------------
Option Explicit

Function WorkTime( _
dStart As Date, _
dEnd As Date, _
hInTime As Date, _
hOutTime As Date, _
Optional adHolidays As Variant)
Dim hStart As Date
Dim bStartIsWorkday As Boolean
Dim hEnd As Date
Dim bEndIsWorkday As Boolean
Dim dwStart As Date
Dim dwEnd As Date
Dim lWorkdays As Long


' Isolate hours from days
dwStart = Int(dStart)
dwEnd = Int(dEnd)
hStart = dStart - dwStart
hEnd = dEnd - dwEnd

' Check if dStart/dEnd is a Workday
bStartIsWorkday = (Networkdays(dwStart, dwStart, adHolidays) > 0)
bEndIsWorkday = (Networkdays(dwEnd, dwEnd, adHolidays) > 0)

'Resolve Start and End times to Working hours
If hStart < hInTime Then hStart = hInTime
If hEnd > hOutTime Then hEnd = hOutTime

WorkTime = 0
If dwStart = dwEnd Then 'All on same day
'Calculate duration for first and only day
If bStartIsWorkday Then WorkTime = hEnd - hStart
Else 'Calculate duration for first day
If bStartIsWorkday And (hStart < hOutTime) Then WorkTime = hOutTime -
hStart
'Calculate duration for last day
If bEndIsWorkday And (hEnd > hInTime) Then WorkTime = WorkTime + (hEnd -
hInTime)
End If
'Calculate duration for elapsed whole workdays
lWorkdays = Networkdays(dwStart, dwEnd, adHolidays)
If lWorkdays >= 3 Then
WorkTime = WorkTime + (lWorkdays - 2) * (hOutTime - hInTime)
End If
End Function
'---------------------

Below formula should work for the cell in column C. You may need to turn on
the analysis toolpak Excel Addin for this to work. Networkdays will also
allow you to define holidays as an option (i.e.
NETWORKDAYS(A2,B2,RANGE_HOLIDAYS)). S_TIME and E_TIME are just named ranges
that I used to enter start and end time once instead of repeating in the
formula, the will need to be formated as time for this to work. You may need
a few more if statements to make sure that the start and end time of the job
is within office hours, else the calculating will start substracting time.

Hope this helps,

felix

=IF(NETWORKDAYS(A2,B2)=1,B2-A2*24,IF(NETWORKDAYS(A2,B2)=2,(E_Time-(A2-ROUNDDOWN(A2,0))+((B2-ROUNDDOWN(B2,0))-S_Time))*24,(NETWORKDAYS(A2,B2)*(E_Time-S_Time))*24+(E_Time-(A2-ROUNDDOWN(A2,0))+((B2-ROUNDDOWN(B2,0))-S_Time))*24))

"Altec101" wrote:

>
> Hello Folks,
>
> I need to create a macro that will be able to determine the total
> amount of time from 8:30am till 4:30pm between two columns (column A
> holds the start time and column B holds the close time) and have it
> insert into column C the total amount of time calculated between two
> dates and times between the hours of 8:30am and 4:30pm Monday through
> Friday, not including holidays.
> The start date could be something like 16/05/2006 1:30:02 PM and the
> end date could be 18/05/2006 3:30:02 PM, 2 days from the start time. I
> need the total amount of time there was from 1:30pm till 4:30pm on
> 16/05/2006 the total amount of time for the next day between 8:30 and
> 4:30 on 17/05/2006 and the total amount of time on the closing date
> between 8:30 and 3:30.(3:30 being the time the report was closed).
> I only need the times between business hours for each day (which are
> 8:30am to 4:30pm) from Monday to Friday and have it exclude holidays
> too.
>
> Example #2: If a report was created after 4:30pm(start time) and not
> closed until 1:30pm(closed time) the next day, the only time counted
> would be from 8:30 until 1:30 the following day, because the only time
> that falls between 8:30 to 4:30 time is on the following day.
>
> Example#2: If a report was created Monday at 11:30am (start time) and
> not closed until 3:30pm (closed time) Wednesday, then the time counted
> would be from 11:30am until 4:30pm same day the report was created,
> Tuesday from 8:30am to 4:30pm and Wednesday from 8:30am until 3:30pm
> because the report was open from Monday to Wednesday and only the time
> between 8:30 and 4:30 was counted for them days.
>
>
> --
> Altec101
> ------------------------------------------------------------------------
> Altec101's Profile: http://www.excelforum.com/member.php...o&userid=34539
> View this thread: http://www.excelforum.com/showthread...hreadid=543329
>
>

You could do that with this formula

=(NETWORKDAYS(A2,B2)-1)/3+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),11/16,17/48),11/16)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),11/16,17/48)

format as [h]:mm

where your start time/date is in A2 and end time/date in B2

This is based on calculating hours between 08:30 and 16:30 MF only, if you don't want to "hard-code" the daily start and finish times you could put those in cells and reference those cells instead.

Note you can also include a holiday range if required

Thanks guys for all your help.

I'll try some of these day to see if they produce the correct results i'm looking for.