Previous Topic Next Topic

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

Answer 1 / 1
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
Ask a question