To find "Date" before a set of days (excluding Sundays).

[krishna334]

To find "Date" before a set of days (excluding Sundays).


Eg:- I want to find the Date which is "60 days" before "3/24/2010" – not including Sundays

In column A --> "Date" - eg: 3/24/2010
In Column B --> No. of Days Before - eg: 60
In Column C --> I want to calculate the Date before the "no of days" set in Column B. This should exclude Sundays.


Please help

 

[Ganjin]

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0><COLGROUP><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=52 height=20>DATE

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>BEFORE</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>DATE BEFORE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=52 height=20>3/30/10</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>60</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>1/29/10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=52 height=20>3/31/10</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>60</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=52 height=20>4/1/10</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>60</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>1/31/10

</TD></TR></TBODY></TABLE>
the formula in C1 is:
=IF(WEEKDAY(A2-B2)=7,"",A2-B2)

 

[krishna334]

The formula seems to need a modification.
Suppose the date is 29/01/2011 then C1 should show 20/11/2011 but the formula gives result 30/11/2011.

hope you can modify the formula easily.

Anyway thanks for the effort.

 

[Ganjin]

you used 60 days back for the test?

 

[barry houdini]

I think krishna334 wants to subtract 60 workdays from the start date, assuming Sunday to be the only non-workday in the week....

If you have Excel 2010 you can use this formula

=WORKDAY.INTL(A2,-B2,11)

where A2 is start date and B2 contains 60

....or in earlier versions you can use an "array formula", i.e.

=A2-SMALL(IF(WEEKDAY(A2-ROW(INDIRECT("1:"&B2*10)))>1,ROW(INDIRECT("1:"&B2*10))),B2)

confirmed with CTRL+SHIFT+ENTER