asked on

Excel: Business hours

Hi Experts - How to determine exact date and time of when I need to start working if have, say, net of 26 business hours, in order to make it on a given date and time? I need to exclude weekends, holidays and off business time. My work hours are 9 AM - 5 PM. The target time is always business hours as well.

Cheers, V

devind

check following
http://www.mrexcel.com/forum/showthread.php?t=15755

NewToVBA

ASKER

Yeah, I saw it before, but the thread does not address question like mine, i.e., the starting date and time is unknown. The known is target date/ time and counted from it a number of business hours. It is a whole different issue.

Cheers, V

Saqib Husain

Try the attached UDF with the following formula

=Bworkday(target date,hours,{"9/5/11","2/5/11","28/4/11"})

My dates are in dd/mm/yy format so you may have to change yours.

Saqib

Function Bworkday(edate, hrs, hols)
For i = 1 To UBound(hols)
hols(i) = Format(hols(i), "dd/mm/yy")
Next i
hrslastday = (edate - Int(edate)) * 24 - 9
If hrs < hrslastday Then Bworkday = edate - hrs / 24: Exit Function
sdate = Int(edate - 1)
hrsleft = hrs - hrslastday
Do While hrsleft >= 8
hol = Application.Match(Format(sdate, "dd/mm/yy"), hols, 0)
If IsError(hol) Then hol = False Else hol = True
If Format(sdate, "ddd") = "Sun" Or Format(sdate, "ddd") = "Sat" Or hol Then
sdate = sdate - 1
Else
sdate = sdate - 1
hrsleft = hrsleft - 8
End If
Loop
hol = Application.Match(Format(sdate, "dd/mm/yy"), hols, 0)
If IsError(hol) Then hol = False Else hol = True
Do While Format(sdate, "ddd") = "Sun" Or Format(sdate, "ddd") = "Sat" Or hol
sdate = sdate - 1
hol = Application.Match(sdate, hols, 0)
If IsError(hol) Then hol = False Else hol = True
Loop
Bworkday = sdate + (17 - hrsleft) / 24
End Function

Open in new window

Saqib Husain

Make sure you test all scenarios in case I have missed something.

NewToVBA

ASKER

Unfrortunately the formula gives me a response "#value!" no matter what I enter, e.g.,
Bworkday(a2,18,Holidays)
a2=05/02/11
Holidays = an array
Any suggestions?

Tks! V

Saqib Husain

Try to enter it as I have shown

=Bworkday(a2,18,{"9/5/11","2/5/11","28/4/11"})

If it works then we can think of how to use a range

NewToVBA

ASKER

Yes, this format works! Would greately appreciate to have a range name support :-)

thank you, V

Saqib Husain

Can you upload your file with only A2 and the Holidays range populated?

Saqib Husain

Try adding this as the first line of code (below the sub statement)

If TypeOf hols Is Range Then hols = WorksheetFunction.Transpose(hols.Value)

NewToVBA

ASKER

Here you go. Thank you, V
When-to-start-knowing-target-dt-.xls