Excel formula which calculates start date

Closed
lsolomon Posts 1 Registration date Friday 31 August 2012 Status Member Last seen 31 August 2012 - 31 Aug 2012 à 18:37
ExcelJosh Posts 12 Registration date Wednesday 8 August 2012 Status Contributor Last seen 12 October 2012 - 5 Sep 2012 à 09:02
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!!



Related:

1 response

ExcelJosh Posts 12 Registration date Wednesday 8 August 2012 Status Contributor Last seen 12 October 2012
5 Sep 2012 à 09:02
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