Hi all,
I am working on a sheet where I need to calculate the estimated date and time of completion for an item.
The completion date and time, however, needs to exclude weekends, holidays (in a named range called "Holidays" in the example sheet), must only count the hours that fall between the working hours of 08:30 and 17:00, and must only show a result with a time that fall between 08:30 and 17:00.
Is this possible?
Many thanks in advance!
If the added date and time is always during business time, please try and copy down:Formula:
Please Login or Register to view this content.
Hi!
Thank you so much, that is perfect! I've been stuck playing with this for 3 days so I can't thank you enough.
If you have time, would you mind explaining what the parts of the formula do so I can learn for next time?
s,TIME(8,30,0): s: The starting time of a business day
e,TIME(17,0,0): e: The ending time of a business day
l,e-s: l: The length of a business day
x,MOD(F3,l), x: The additional time required after subtracting as many full business days as possible
h,WORKDAY(D3,F3/l+(x+E3>e), h: The date the work is completed
x+E3-l*(x+E3>e): The time the work is completed
You're welcome. Thanks for the feedback and glad to have helped..
Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Thank you for taking the time to explain, and for your help overall!
Thanks for the feedback and rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks