Previous Topic Next Topic

Excel formula which calculates start date

Closed
lsolomon Posts 1 Registration date Friday August 31, 2012 Status Member Last seen August 31, 2012 - Aug 31, 2012 at 06:37 PM
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012 - Sep 5, 2012 at 09:02 AM
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
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012
Sep 5, 2012 at 09:02 AM
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
0