Excel formula which calculates start date [Closed]

Report
Posts
1
Registration date
Friday August 31, 2012
Status
Member
Last seen
August 31, 2012
-
Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012
-
Hello,

I would like to create a calendar tool that will generate a start date from a set end date that excludes weekends based on processing and transit times

example

processing time =4 days + 1 day (order date)
transit time =6 days
the last delivery date is 11/21
whats the last day to order the item to receive it by 11/21?
the answer should be 11/7

processing time /transit time/ padding /last delivery date/order by date
4 6 1 11/21 11/7


i tried these formulas but it didnt completely work


=WEEKDAY(H2,2)
=IF(I2=6, H2-1,H2)
=IF(I2=7,H2-2,H2)
=IF(H2=J2=K2,H2,IF(H2<>J2,J2,IF(H2<>K2,K2,H2)))

Plus i have a feeling there are more than i need

HELP!!



1 reply

Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012

Isolomon:

I hope this helps.

=WORKDAY(start_date, -1*(SUM(range)-1))

This doesn't take into account for Holidays. You would have to list the holidays you'd like to exclude.

Good luck,
Josh

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!