• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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
0
NewToVBA
Asked:
NewToVBA
  • 6
  • 5
1 Solution
 
devindCommented:
0
 
NewToVBAAuthor 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
0
 
Saqib Husain, SyedEngineerCommented:
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

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Make sure you test all scenarios in case I have missed something.
0
 
NewToVBAAuthor 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
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
NewToVBAAuthor Commented:
Yes, this format works! Would greately appreciate to have a range name support :-)

thank you, V
0
 
Saqib Husain, SyedEngineerCommented:
Can you upload your file with only A2 and the Holidays range populated?
0
 
Saqib Husain, SyedEngineerCommented:
Try adding this as the first line of code (below the sub statement)

If TypeOf hols Is Range Then hols = WorksheetFunction.Transpose(hols.Value)
0
 
NewToVBAAuthor Commented:
Here you go. Thank you, V
When-to-start-knowing-target-dt-.xls
0
 
Saqib Husain, SyedEngineerCommented:
For some reason or the other my excel does not like the range name and excel crashes whenever I enter the range name in the function on the sheet. So I am unable to test it. I shall try to find another computer to do this.

What happens at your end when you enter the line I proposed above?
0
 
NewToVBAAuthor Commented:
It works perfect with the new line of code.
Many thanks, ssaqikh!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now