We help IT Professionals succeed at work.

Excel: Business hours

NewToVBA
NewToVBA asked
on
Medium Priority
320 Views
Last Modified: 2012-06-27
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
Comment
Watch Question

Author

Commented:
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 HusainEngineer
CERTIFIED EXPERT

Commented:
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 HusainEngineer
CERTIFIED EXPERT

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

Author

Commented:
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 HusainEngineer
CERTIFIED EXPERT

Commented:
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

Author

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

thank you, V
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Can you upload your file with only A2 and the Holidays range populated?
Saqib HusainEngineer
CERTIFIED EXPERT

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

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

Author

Commented:
Here you go. Thank you, V
When-to-start-knowing-target-dt-.xls
Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It works perfect with the new line of code.
Many thanks, ssaqikh!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.