Date formula

[DanWo]

I would like to calculate :
End Date (B1)
from a Start Date (A1)
+ Duration (C1) in Working days (without WE & holidays)
BUT
if cell $D$1=1 ,Saterdays are working days too.

WORKDAY function consider only monday to Friday as working days!
How to do it?

 

[Chattin]

Have a look at NETWORKDAYS in the xl help to get you a bit closer to your answer

 

[DanWo]

Networksdays return the number of whole workdays between 2 dates; it can not calculate EnDate.

 

[Chattin]

Yes - I know that and a solution can be worked from there therefore its a start to the OP's query as networkdays allows for holidays and weekends

This will be useful too - from

http://www.cpearson.com/excel/DateTimeWS.htm

Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

 

[Chattin]

Bleh! Brain not working - misunderstood your post - looking again - sorry!

 

[Chattin]

Firstly do you mean you want to calculate B1 (end date) from A1 (start date) plus a value in C1 allowing for W/E and hols?

 

[Chattin]

A start - working on the condition part:

B1=A2+NETWORKDAYS(A1,A2)-DATEDIF(A1,A2,"d&quot

where A2=A1+C1 and C1 is a number of days

 

[Chattin]

Try this - array entered

=IF(D1<>1,A2+NETWORKDAYS(A1,A2)-DATEDIF(A1,A2,&quot;d&quot,A2+NETWORKDAYS(A1,A2)-DATEDIF(A1,A2,&quot;d&quot+SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT(&quot;1:&quot;&TRUNC(A2-A1)+1)))=C2,1,0)))

c2=7 (for saturday)

HTH

 

[Chattin]

Oh - I should say the syntax for networkdays is NETWORKDAYS(start_date,end_date,holidays)

so you'll have to enter an array for the holidays field too!

HTH

 

[DanWo]

result is not correct...don't find the error!

 

[Chattin]

In B2 did you array enter the formula (Ctrl+Shift+Enter) as opposed to a normal Enter? Did you enter a list (array) of holidays for the NETWORKDAYS part of the formula?

 

[DanWo]

Yes for both.
I try the following:
=IF(D1<>1,WORKDAY(A1,C1),A2+NETWORKDAYS(A1,A2)-DATEDIF(A1,A2,&quot;d&quot+SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT(&quot;1:&quot;&TRUNC(A2-A1)+1)))=C2,1,0)))

 

[Chattin]

OK - back to basics in case I've misunderstood your original post -as I said before - do you mean you want to calculate B1 (end date) from A1 (start date) plus a value in C1 (i.e. a number of days) allowing for W/E and hols?

 

[DanWo]

Excact ,with the following conditions:

If $D$1 =1: working days are monday to saterday excluding holidays
If $D$1<>1: working days are monday to friday excl.holidays

 

[Chattin]

Could you post some data and expected results pls?

 

[DanWo]

I can email file if it's helpful for you .

 

[DanWo]

Don't know how to post file

 

[Chattin]

Sorry - most of the boards I post to have a facility to post a HTML version of a spreadsheet - don't know how to do it here. Mail me at chrisjchattin(at)yahoo.co.uk - replace the (at) as required - just stops the spiders