Same serial day of previous month

[amandeep08]

I want the same serial of day of last month.

E.G. if today is 9th Jul'11 which is 2nd saturday of the month, i want the result as 11th jun'11 which is also the 2nd saturday of the month.

If there is any excel formula please let me know.

Thanks in advance.

 

[jasonb75]

What answer would be 'correct' if today was 30th Jul'11?

That date is the 5th saturday of the month, but there is no 5th saturday in the previous month.

 

[Robert Mika]

Hi amend and welcome to the board.
The result of...?

 

[amandeep08]

Dear Jasonb,

In that case i have to show the other data with IF condition.

 

[jasonb75]

Deleted, found error in formula after posting.

 

[markmzz]

Try this:

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 style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Sat 9-Jul-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Sat 11-Jun-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Sat 11-Jun-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e3">Sat 30-Jul-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e3">Sat 2-Jul-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e3">Sat 25-Jun-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">Sun 16-May-10</TD><TD style="TEXT-ALIGN: right">Sun 18-Apr-10</TD><TD style="TEXT-ALIGN: right">Sun 18-Apr-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">Tue 19-Jul-11</TD><TD style="TEXT-ALIGN: right">Tue 21-Jun-11</TD><TD style="TEXT-ALIGN: right">Tue 21-Jun-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">Wed 30-Nov-11</TD><TD style="TEXT-ALIGN: right">Wed 2-Nov-11</TD><TD style="TEXT-ALIGN: right">Wed 26-Oct-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">Wed 14-Jul-10</TD><TD style="TEXT-ALIGN: right">Wed 9-Jun-10</TD><TD style="TEXT-ALIGN: right">Wed 9-Jun-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">Thu 16-Sep-10</TD><TD style="TEXT-ALIGN: right">Thu 19-Aug-10</TD><TD style="TEXT-ALIGN: right">Thu 19-Aug-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">Sat 3-Dec-11</TD><TD style="TEXT-ALIGN: right">Sat 5-Nov-11</TD><TD style="TEXT-ALIGN: right">Sat 5-Nov-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">Thu 5-Aug-10</TD><TD style="TEXT-ALIGN: right">Thu 1-Jul-10</TD><TD style="TEXT-ALIGN: right">Thu 1-Jul-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">Wed 31-Mar-10</TD><TD style="TEXT-ALIGN: right">Wed 3-Mar-10</TD><TD style="TEXT-ALIGN: right">Wed 24-Feb-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">Tue 28-Sep-10</TD><TD style="TEXT-ALIGN: right">Tue 24-Aug-10</TD><TD style="TEXT-ALIGN: right">Tue 24-Aug-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">Mon 17-May-10</TD><TD style="TEXT-ALIGN: right">Mon 19-Apr-10</TD><TD style="TEXT-ALIGN: right">Mon 19-Apr-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">Fri 10-Jun-11</TD><TD style="TEXT-ALIGN: right">Fri 13-May-11</TD><TD style="TEXT-ALIGN: right">Fri 13-May-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">Sun 3-Apr-11</TD><TD style="TEXT-ALIGN: right">Sun 6-Mar-11</TD><TD style="TEXT-ALIGN: right">Sun 6-Mar-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">Sat 16-Jul-11</TD><TD style="TEXT-ALIGN: right">Sat 18-Jun-11</TD><TD style="TEXT-ALIGN: right">Sat 18-Jun-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">Fri 18-Jun-10</TD><TD style="TEXT-ALIGN: right">Fri 21-May-10</TD><TD style="TEXT-ALIGN: right">Fri 21-May-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right">Thu 27-Oct-11</TD><TD style="TEXT-ALIGN: right">Thu 22-Sep-11</TD><TD style="TEXT-ALIGN: right">Thu 22-Sep-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right">Sun 25-Sep-11</TD><TD style="TEXT-ALIGN: right">Sun 28-Aug-11</TD><TD style="TEXT-ALIGN: right">Sun 28-Aug-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right">Sat 17-Apr-10</TD><TD style="TEXT-ALIGN: right">Sat 20-Mar-10</TD><TD style="TEXT-ALIGN: right">Sat 20-Mar-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right">Thu 6-Jan-11</TD><TD style="TEXT-ALIGN: right">Thu 2-Dec-10</TD><TD style="TEXT-ALIGN: right">Thu 2-Dec-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: right">Sat 10-Sep-11</TD><TD style="TEXT-ALIGN: right">Sat 13-Aug-11</TD><TD style="TEXT-ALIGN: right">Sat 13-Aug-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="TEXT-ALIGN: right">Thu 12-Aug-10</TD><TD style="TEXT-ALIGN: right">Thu 8-Jul-10</TD><TD style="TEXT-ALIGN: right">Thu 8-Jul-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD style="TEXT-ALIGN: right">Mon 11-Oct-10</TD><TD style="TEXT-ALIGN: right">Mon 13-Sep-10</TD><TD style="TEXT-ALIGN: right">Mon 13-Sep-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD style="TEXT-ALIGN: right">Sat 30-Jan-10</TD><TD style="TEXT-ALIGN: right">Sat 2-Jan-10</TD><TD style="TEXT-ALIGN: right">Sat 26-Dec-09</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD style="TEXT-ALIGN: right">Fri 2-Apr-10</TD><TD style="TEXT-ALIGN: right">Fri 5-Mar-10</TD><TD style="TEXT-ALIGN: right">Fri 5-Mar-10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Mon 7-Mar-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Mon 7-Feb-11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">Mon 7-Feb-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD>**************</TD><TD>**************</TD><TD>**************</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">=MAX((WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,{1;2;3;4;5;6;7}),2)=WEEKDAY(A2,2))*DATE(YEAR(A2),MONTH(A2)-1,{1;2;3;4;5;6;7}))+7*(INT((DAY(A2)-1)/7))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=MAX((WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,{1;2;3;4;5;6;7}),2)=WEEKDAY(A2,2))*DATE(YEAR(A2),MONTH(A2)-1,{1;2;3;4;5;6;7}))+7*(INT((DAY(A2)-1)/7))-IF(CEILING(DAY(A2),7)=35,7,0)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A4</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(40179,40908)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[jasonb75]

I'm not convinced this is what you want, but it is what you asked for. Looking at Mark's suggestions I would guess it's not just me that finds your question a little vague.

Where A1 holds the date of interest, ie. =TODAY() based on your example.

=IF(DAY(A1)>28,"Show other data!",A1-IF((FLOOR(DAY(A1)-1,7))=FLOOR(DAY(A1-28)-1,7),28,35))

 

[amandeep08]

Dear markmzz

Its excellent. Thanks a lot. You have solved my big problem.

Thanks a lot once again.

 

[jasonb75]

You can get the same results with less effort,

Same result as Mark's first formula

=A2-IF(OR(DAY(A2)>28,CEILING(DAY(A2),7)=CEILING(DAY(A2-28),7)),28,35)

Same as second

=A2-IF(CEILING(DAY(A2),7)=CEILING(DAY(A2-28),7),28,35)

I'm fairly sure it can also be done with MROUND() as well but I don't have consistant results.

 

[markmzz]

Dear markmzz

Its excellent. Thanks a lot. You have solved my big problem.

Thanks a lot once again.

Thanks for the feedback.

Note: look at the Jasonb75 formula.

Markmzz