Month from 26 to 25th.

[shonaart]

Hi
My month runs from the 26 of the Month to 25th of the Month.

Column A : Has dates, sort from old to present. ( 28 June 2011 )

Column B : I would like a formula to look at column A : and show the month. Remember the month runs from the 26 May 2011 to 25 June 2011 is June 2011 and 26 June to 25 July = July 2011. Etc

Thanks
Vaughan

 

[Yahya]

try this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">28/June/2011</td><td style="text-align: right;;">28/July/2011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">A2</font>)>26,EDATE(<font color="Red">A2,1</font>),A2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[shonaart]

Thanks Yahya, Works great
Thanks you for your help

 

[Biz]

I was thinking of another way


Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 163px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Month Date</TD><TD>Month Ends Dates</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/06/11</TD><TD>26 May 2011- 25 Jun 2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=TEXT(DATE(YEAR(A2),MONTH(A2)-1,26),"DD MMM YYYY")&"- "&TEXT(DATE(YEAR(A2),MONTH(A2),25),"DD MMM YYYY")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Biz

 

[Biz]

Try formula below

=IF(DAY(A2)>=26,TEXT(DATE(YEAR(A2),MONTH(A2),26),"DD MMM YYYY"),TEXT(DATE(YEAR(A2),MONTH(A2)-1,26),"DD MMM YYYY"))&" - "&IF(DAY(A2)<26,TEXT(DATE(YEAR(A2),MONTH(A2),25),"DD MMM YYYY"),TEXT(DATE(YEAR(A2),MONTH(A2)+1,25),"DD MMM YYYY"))

 

[Yahya]

Thanks Yahya, Works great
Thanks you for your help

you are welcome