calculate end date excl holidays

[dave bates]

Hi i am using excel 2003 and want to calculate the end date of a process based on the start date + duration in days but adjusted for holiday periods eg start date in B3, duration in B4, holidays in A20:A30.

the duration is in days and does not need adjusting for weekends



thanks for any help

 

[jasonb75]

Assuming holidays are a full list of dates, not just start and end date of a holiday peroid.

=sum(B3,B4,count(A20:A30))-1

This includes the start date in the duration, remove the -1 from the end if it should be excluded.

 

[dave bates]

thanks, I realised that I did not explain it very well.

The duration needs to take account of any holidays that may fall in the period eg if start date was the 26 April, the duration 14 days therefore end = 10 May however 28 April is a holiday therefore end date = 11 May

sorry for the confusion

 

[barry houdini]

Try

=SMALL(IF(COUNTIF(A20:A30,B3+ROW(INDIRECT("1:"&B4*10)))=0,ROW(INDIRECT("1:"&B4*10))),C3)+B3

confirmed with CTRL+SHIFT+ENTER

or if you have Excel 2010 you can use WORKDAY.INTL function for the same result using

=WORKDAY.INTL(B3,B4,"0000000",A20:A30)

 

[dave bates]

thanks for your reply, I am not sure what the C3 is referencing to.

Also to make matters worst for me! I would like to reformat the data such that the start date is in B3 the duration in C3 holidays in A20:A30 [singular dates in each cell]

 

[barry houdini]

Sorry the C3 should have been B4 based on your original setup....but if you have start date in B3, duration in C3 and holidays in A20:A30 then try

=SMALL(IF(COUNTIF(A$20:A$30,B3+ROW(INDIRECT("1:"&C3*10)))=0,ROW(INDIRECT("1:"&C3*10))),C3)+B3

confirmed with CTRL+SHIFT+ENTER

 

[dave bates]

brilliant

thank you very much