subtracting dates to count days(no sunday)

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,356

Office Version

1. 365

Platform

1. Windows

• Aug 31, 2006

• #2

Assuming 1st date in A2, 2nd date in B2.

Try this:

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

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Aug 31, 2006

• #3

Or..

=SUMPRODUCT(--(ISNA(MATCH(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),1),{1},0))))

where StartDate in A1 and EndDate in A2

HTH

 

Upvote 0

J

jek40

Active Member

Joined

Jan 17, 2005

Messages

317

• Aug 31, 2006

• #4

thanks guys

both work great!!

Krishnakumar -- for some reason I have to subtract 1 from the result to get the total I am looking for but no problem I can make that adjustment.

thanks to both of you for the help

John

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Aug 31, 2006

• #5

=B1-A1-INT((B1-A1+WEEKDAY(A1-1))/7)+1

 

Upvote 0

J

jek40

Active Member

Joined

Jan 17, 2005

Messages

317

• Sep 1, 2006

• #6

Thanks fairwinds

another great idea!!

thanks,
John

 

Upvote 0