Not really a question . . . . . . . . . .

[adulted]

Current Calendar

Can't remember where I got this one from:

B1 =now() formattted as Month 2006 (mmmm yyyy)
B2:H2 Sun, Mon, Tue, Wed, Thu, Fri, Sat
Select range B3:H7 enter the following:

=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

CSE
formatted as custom d

Paul

Excel Formulas.xls
	A	B	C	D	E	F	G	H	I
1		June 2006
2		Sun	Mon	Tue	Wed	Thu	Fri	Sat
3						1	2	3
4		4	5	6	7	8	9	10
5		11	12	13	14	15	16	17
6		18	19	20	21	22	23	24
7		25	26	27	28	29	30
Month Calendar

 

[Greg Truby]

Well, while it certainly resembles the skill-level of a few board members I could name, I personally hadn't seen it before. Nifty handiwork by whoever did indeed stitch it together.

 

[njimack]

See comment 6 on http://www.dicks-blog.com/archives/2004/06/23/ugly-formulas/

 

[SydneyGeek]

Well, while it certainly resembles the skill-level of a few board members I could name, I personally hadn't seen it before. Nifty handiwork by whoever did indeed stitch it together.

Not entirely sure, but John Walkenbach put together a calendar formula in one of his books. It may be the same one...

Denis

 

[Richard Schollar]

Denis, nobody could ever accuse you of being too hasty when it comes to formulating a reply...

 

[SydneyGeek]

Denis, nobody could ever accuse you of being too hasty when it comes to formulating a reply...

Yep, my middle name's Quick-draw!

Actually, I kinda got directed to that post tonight and thought I'd throw in my 2 cents...

Denis

 

[MarkAndrews]

A few from me

=IF(ISNUMBER(D5),IF(N(E5),E5,IF(SUMIF(E5,"*(*",D5),LEFT(E5,SEARCH("(",E5&"(")-1),LEFT(E5,SUMPRODUCT(LEN(E5)-LEN(SUBSTITUTE(E5,{0,1,2,3,4,5,6,7,8,9},""))))))/D5,"")

=TRIM(TEXT(EOMONTH($D$3,MID($C4,SEARCH("-",$C4,1),4)),"DD MMM YYY"))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>=IF(YEAR(G$14)=2007,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol07),IF(YEAR(G$14)=2008,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol08),IF(YEAR(G$14)=2009,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol09),IF(YEAR(G$14)=2010,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol10),IF(YEAR(G$14)=2011,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol11),IF(YEAR(G$14)=2012,NETWORKDAYS(EOMONTH(G14,-1)+1,EOMONTH(G14,0),Hol12)))))))

=IF(ISNA(HLOOKUP(MID($A6,6,2),'[Brand_rebates Retail.xls]Report Feed'!$C$5:$P$30,MATCH("Grand Total",'[Brand_rebates Retail.xls]Report Feed'!$A$5:$A$30,0),0)),"0",HLOOKUP(MID($A6,6,2),'[Brand_rebates Retail.xls]Report Feed'!$C$5:$P$30,MATCH("Grand Total",'[Brand_rebates Retail.xls]Report Feed'!$A$5:$A$30,0),0))<o></o>
<o></o>
<o>=IF(ISNA(VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)),"",IF(ISERROR(VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)),"",VLOOKUP(LEFT(G3,SEARCH(" ",G3)-1),'[Copy of Supplier Payment Terms.xls]Live'!$A:$H,3,0)))<o></o>
</o></o>

 

[Lewiy]

One I’ve just had to construct for verifying that data has been correctly input into a worksheet:

=IF(A3="","",IF(OR(LEN(C3)<>6,AND(LEFT(C3,1)<>"E",LEFT(C3,1)<>"C")),"A/C#",IF(AND(LEFT(C3)="E",OR(G3="",F3<>"")),"Euroclear",IF(AND(LEFT(C3)="C",OR(G3<>"",F3="")),"Clearstream",IF(OR(AND(LEFT(A3)="1",OR(K3<>"X",J3<>"")),AND(LEFT(A3)="2",OR(K3<>"",J3<>"X"))),"MTN/ECP",IF(SUM(F3:G3)<>P3,"Value",""))))))&" - "&IF(A3="","",IF(OR(AND(D3<>H3,ISERROR(FIND("S",O3))),AND(NOT(ISERROR(FIND("S",O3))),D3=H3)),"Swap?",IF(OR(AND(B3=E3,ISERROR(FIND("V",O3))),AND(NOT(ISERROR(FIND("V",O3))),B3<>E3)),"Check Issuer",IF(N3<>VALUE(RIGHT(CELL("FILENAME",T3),LEN(CELL("FILENAME",T3))-FIND("]",CELL("FILENAME",T3)))),"Date",IF(COUNTIF(A:A,A3)>1,"Duplicate","")))))&" - "&IF(OR(AND(LEFT(E3,3)="RBS",LEN(E3)<>8),AND(E3="ULSTER",LEN(E3)<>11)),"PROGRAMME NUMBER",IF(OR(E3="BANK SCOTLAND",E3="WESTPAC",E3="NORD DLB"),"ISSUER ENTITY",""))

 

[MarkAndrews]

One I’ve just had to construct for verifying that data has been correctly input into a worksheet:

=IF(A3="","",IF(OR(LEN(C3)<>6,AND(LEFT(C3,1)<>"E",LEFT(C3,1)<>"C")),"A/C#",IF(AND(LEFT(C3)="E",OR(G3="",F3<>"")),"Euroclear",IF(AND(LEFT(C3)="C",OR(G3<>"",F3="")),"Clearstream",IF(OR(AND(LEFT(A3)="1",OR(K3<>"X",J3<>"")),AND(LEFT(A3)="2",OR(K3<>"",J3<>"X"))),"MTN/ECP",IF(SUM(F3:G3)<>P3,"Value",""))))))&" - "&IF(A3="","",IF(OR(AND(D3<>H3,ISERROR(FIND("S",O3))),AND(NOT(ISERROR(FIND("S",O3))),D3=H3)),"Swap?",IF(OR(AND(B3=E3,ISERROR(FIND("V",O3))),AND(NOT(ISERROR(FIND("V",O3))),B3<>E3)),"Check Issuer",IF(N3<>VALUE(RIGHT(CELL("FILENAME",T3),LEN(CELL("FILENAME",T3))-FIND("]",CELL("FILENAME",T3)))),"Date",IF(COUNTIF(A:A,A3)>1,"Duplicate","")))))&" - "&IF(OR(AND(LEFT(E3,3)="RBS",LEN(E3)<>8),AND(E3="ULSTER",LEN(E3)<>11)),"PROGRAMME NUMBER",IF(OR(E3="BANK SCOTLAND",E3="WESTPAC",E3="NORD DLB"),"ISSUER ENTITY",""))

Beats my efforts!

 

[Lewiy]

Beats my efforts!

There’s probably a more efficient way of doing what it does but it has just grown gradually over the last week or so as I kept adding bits to verify different stuff!!!