Hi all
I'm struggling a bit to get Excel to calculate an expected time of delivery when I need to count only on workdays (Monday-Friday).
I basically have these columns:
Column 1: Shipment date (given)
Column 2: Shipping days (given)
Column 3: Expected time of delivery (calculated)
So if I e.g. have a shipment date Friday 16.11.2007 with 3 days delivery time I need to have a 21.11.2007 output.
And is it possible to have Excel take public holidays into acount as well?
Thanks,
Kjaer
Excel's WORKDAY function calculates the workday that is a specified number of days before or after a given date. It also takes holidays (which you identify) into consideration when calculating dates.
Look up that function in Excel Help. Oh, you'll need to have the Analysis ToolPak (ATP) add-in (comes with Excel) to use that formula. If you don't have it (or don't want it), there are other options, but none of them are as elegant as WORKDAY. (The ATP also has a bunch of other neat functions.)
Does that help?
Post back if you have more questions.
Try
=Workday(A2,B2,Holidays) where A2 is start date, B2 is no. of days and Holidays is a named range in your spreadsheet containing the holidays to exclude.
Note: Workday() is an analysis toolpak addin function... go to Tools|Addins. The Analysis Toolpak should be checked.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks