counting n days backwards.

[deuce]

Hi, is there some method which can be devised to count back n number of days from a day/date of the week only if the day falls from Monday to Friday?

So for example today is Saturday, I want to count 7 working days backwards from today's date as the days/dates of Saturday and Sunday that fell in between would be excluded.

what would the date/day have been 7 working days in the past?

thanks and regards.

 

[Andrew Poulsom]

You can use the WORKDAY function. Prior to Excel 2007 it's in the Analysis ToolPak Add-In, which must be installed.

 

[deuce]

hi thanks for your response,

what should the content for the formula be for the correct results to be displayed?

saturday and sunday need to be factored out.

I tried to use the formula, and got Friday, August 05, 2011 as a result trying to calculate 20 days backwards from today (Saturday) which should have been left out in the formula.

 

[Peter_SSs]

You have counted forward 20 days not backwards. Try
=WORKDAY(TODAY(),-20)
For me that gives Monday, 13 June 2011 which is 20 work days before today (Saturday 9 July here at the moment)

 

[deuce]

hi peter, thanks for your response, but i want to leave out the saturdays and sundays in this time bracket. can you please suggest something to accomplish this? The correct answer to be displayed would be June 16,2011.

 

[Peter_SSs]

hi peter, thanks for your response, but i want to leave out the saturdays and sundays in this time bracket. can you please suggest something to accomplish this? The correct answer to be displayed would be June 16,2011.

How do you calculate that?

Here they are, counting back 1 workday at a time for 20 days.

Excel Workbook
	A	B
1	Today	Saturday, 9 July 2011
2	1	Friday, 8 July 2011
3	2	Thursday, 7 July 2011
4	3	Wednesday, 6 July 2011
5	4	Tuesday, 5 July 2011
6	5	Monday, 4 July 2011
7	6	Friday, 1 July 2011
8	7	Thursday, 30 June 2011
9	8	Wednesday, 29 June 2011
10	9	Tuesday, 28 June 2011
11	10	Monday, 27 June 2011
12	11	Friday, 24 June 2011
13	12	Thursday, 23 June 2011
14	13	Wednesday, 22 June 2011
15	14	Tuesday, 21 June 2011
16	15	Monday, 20 June 2011
17	16	Friday, 17 June 2011
18	17	Thursday, 16 June 2011
19	18	Wednesday, 15 June 2011
20	19	Tuesday, 14 June 2011
21	20	Monday, 13 June 2011
WORKDAY

 

[markmzz]

If I understand correct what you want, I think this can help you:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Today</TD><TD style="TEXT-ALIGN: right">Sat 09/07/2011</TD><TD style="TEXT-ALIGN: right">Sat 09/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">Fri 08/07/2011</TD><TD style="TEXT-ALIGN: right">Fri 08/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">Thu 07/07/2011</TD><TD style="TEXT-ALIGN: right">Thu 07/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">Wed 06/07/2011</TD><TD style="TEXT-ALIGN: right">Wed 06/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">Tue 05/07/2011</TD><TD style="TEXT-ALIGN: right">Tue 05/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">Mon 04/07/2011</TD><TD style="TEXT-ALIGN: right">Mon 04/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">Sun 03/07/2011</TD><TD style="TEXT-ALIGN: right">Fri 01/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">Sat 02/07/2011</TD><TD style="TEXT-ALIGN: right">Fri 01/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">Fri 01/07/2011</TD><TD style="TEXT-ALIGN: right">Fri 01/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">Thu 30/06/2011</TD><TD style="TEXT-ALIGN: right">Thu 30/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">Wed 29/06/2011</TD><TD style="TEXT-ALIGN: right">Wed 29/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">Tue 28/06/2011</TD><TD style="TEXT-ALIGN: right">Tue 28/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">Mon 27/06/2011</TD><TD style="TEXT-ALIGN: right">Mon 27/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">Sun 26/06/2011</TD><TD style="TEXT-ALIGN: right">Fri 24/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">Sat 25/06/2011</TD><TD style="TEXT-ALIGN: right">Fri 24/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">Fri 24/06/2011</TD><TD style="TEXT-ALIGN: right">Fri 24/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">Thu 23/06/2011</TD><TD style="TEXT-ALIGN: right">Thu 23/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">Wed 22/06/2011</TD><TD style="TEXT-ALIGN: right">Wed 22/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">Tue 21/06/2011</TD><TD style="TEXT-ALIGN: right">Tue 21/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">Mon 20/06/2011</TD><TD style="TEXT-ALIGN: right">Mon 20/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">Sun 19/06/2011</TD><TD style="TEXT-ALIGN: right">Fri 17/06/2011</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=$B$1-A2</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=$C$1-A2-LOOKUP(WEEKDAY($C$1-A2,2),{0;6;7},{0;1;2})</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[markmzz]

The formula in C2 can be also this:

=$C$1-A2-LOOKUP(WEEKDAY($C$1-A2,2),{1;6;7},{0;1;2})

Markmzz

 

[deuce]

hi thanks for the responses, peter's solution worked perfectly.

thanks your all lifesavers.