?
Solved

Excel: Business hours

Posted on 2011-04-19
12
Medium Priority
?
298 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
0
Comment
Question by:NewToVBA
  • 6
  • 5
12 Comments
 
LVL 4

Expert Comment

by:devind
ID: 35430114
0
 

Author Comment

by:NewToVBA
ID: 35432389
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35439487
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35439488
Make sure you test all scenarios in case I have missed something.
0
 

Author Comment

by:NewToVBA
ID: 35456762
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35456815
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
 

Author Comment

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

thank you, V
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35456952
Can you upload your file with only A2 and the Holidays range populated?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35457000
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
 

Author Comment

by:NewToVBA
ID: 35457011
Here you go. Thank you, V
When-to-start-knowing-target-dt-.xls
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 35457069
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
 

Author Closing Comment

by:NewToVBA
ID: 35457316
It works perfect with the new line of code.
Many thanks, ssaqikh!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question