Posts by Paddyd

Paddyd · Aug 31st 2003

It might help if you were a litle more specific about the nature of the errors you are getting.

Paddyd · Aug 26th 2003

FWIW, you can achieve a similar result using data validation. search the site for 'dynamic lists' or something similar - as I recal, Dave has an example workbook describing a technique...

Paddyd · Aug 26th 2003

although clearly formulas of the form:

=SUM(E1,E2,E3,E4,E5,E6,E7,E8,E9,E10,E11,E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22,E23,E24,E25,E26,E27,E28,E29,E30,E31)

are to be avoided, whether they work or not! Better is to exploit some regularity about the distribution of the cells, assuming they're non-contiguous. Absent any regularity, other approaches are available. So, the question is - how are these cells distributed...?

Paddyd · Aug 13th 2003

see the progressive priving example:

http://www.cpearson.com/excel/pricing.htm

Paddyd · Aug 3rd 2003

Hi.

This should be straightforward, but need to know more about your data:

How are your call times entered? in what range? does the entry include the date? if not, where is the date recorded? what cells are the rates in? what output do you want - simple cost per call? why is the duration of the call example you gave - "starting on Thursday 17:00 and ending on Friday 19:00" - only 2 hours, not 26?

more details please...

Paddyd · Aug 1st 2003

the offending smilie can be removed:

http://www.ozgrid.com/forum/viewthread.php?tid=3787

...well, replaced with a transparent one at least.

Paddyd · Jul 7th 2003

Paddyd · Jul 7th 2003

I have to admit a fondness for Andy Pope's non-vba approach to this:

http://www.andypope.info/fun/picviewer.htm

Paddyd · Jul 6th 2003

"The problem is that you do not have an Excel recognizable date entered into column D. "

2 solutions:

1) use a formula that coerces the text formatted date into a recognised data value, then embed this in the calculation. Given the posted data:

=(TEXT(D2,"DDMMMYYYY")+0)-TODAY()

...which assumes that data like Sep-04 can be interpreted as 01/09/04. also note that given dates both before & after today, datedif is not appropriate because of it's sytntax of datedif(earlydate, latedate,period)

Paddyd · Jul 5th 2003

see the help file for indirect():

=INDIRECT("sheet"&A1&"!A1")

..would return the value in a1 from the sheet number entered in a1 on the sheet the formula's in...

Paddyd · Jul 3rd 2003

one option:

the formula is:

=ISNUMBER(MATCH($D2,A$1:A$4,0))

Paddyd · Jun 11th 2003

see the example:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book6</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb118219><INPUT onclick='window.clipboardData.setData("Text",document.formFb130085.sltNb782009.value);' type=button value="Copy Formula" name=btCb047069></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb130085><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value" name=sltNb782009><OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6)) selected>E2<OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1)),(B2:B6))>E3<OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))+0,(B2:B6))>E4</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6)) name=txbFb216056></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Date</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Hours</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Month</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/01/2002</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/01/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Total 1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb130085.sltNb782009.options[0].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);">#VALUE!</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/01/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Total 2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb130085.sltNb782009.options[1].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);">0</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/01/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">H</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">Total 3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb130085.sltNb782009.options[2].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);">15</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/01/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">1/02/2002</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff">5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

...which includes 3 sumproduct() formulas as examples.

Total 1:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6))

attempts to multiply directly a series of true / false returns with a series of numbers. because there is a text value in the number range, you get the value error - examination of formula components shows that there's only a single array involved - i.e. the results of the multiplication - and that the array fed into the sumproduct is:

{5;5;#value!;5;0}

...hence the error.

Total 2:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1)),(B2:B6))

Rather than doing the array multiplication directly, this version returns 2 arrays that are then fed into the sumproduct. Investigation shows the arrays to be:

{TRUE;TRUE;TRUE;TRUE;FALSE}
and
{5;5;"H";5;5}

however, the formula returns 0 because sumproduct will not treat the logicals as 1 + 0...

so:

Total 3:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))+0,(B2:B6))

uses the +0 to coerce the logicals into numbers (1 & 0), which are then fed into the sumproduct to return the expected results. the arrays in this case are:

{1;1;1;1;0}
and
{5;5;"h";5;5}

...which gives us the result we need.

So - the moral is don't do the array multiplications directly if there are likely to be text values in numeric ranges.

paddy

Paddyd · Jun 9th 2003

a1&" "&b1&" "&c1

Paddyd · Jun 8th 2003

"Working with dates is a real hassle..."

Shouldn't be, as long as you keep one thing in mind:

If you don't enter a genuine date value to represent a date, your life will become hell later.

For example, if 09/06/2003 is a Monday & you want to display the date as Monday, just enter the full date & apply the appropriate format (ddd in this case). having entered a date value, you can still do all the standard calculations - enter Mon as text & about all you can know is that it's a text string of lenght 3.

Always enter a full date value

paddy

Paddyd · Jun 6th 2003

"so a date of 4/15/91 goes in ata number like that but if i can make the date read April 15, 1991 it will transfer in"

sorry - misread your post. you need the format for the transfer to the other program!

as you posted, the text() function can be used to return a formatted text string. If you have a propre date value in the cells, then simply:

=text(a1,"ddd dd/mm/yy")

or whatever should suffice.

Paddyd · Jun 6th 2003

"and it cannot be reformatted "

...why not?

Paddyd · Jun 6th 2003

I don't think you need a formula to 'convert' the value. would simply formatting the cell suffice? (right click | format cell... | select the appropriate date format.

Paddyd · Jun 6th 2003

Hi - welcome to the board.

Your post's not entirely clear, so forgive me if I've mis-interpreted your problems...

Quote

Have looked at the help documentation for joining text and values but still don't understand the spacing for a long string of text with as many as 10 values to be interspersed in the text.

the ampersand is the concatenation operator, so sometihng like:

="text "&a1&" "&B1&" "&C1&" more text"

...etc

Quote

The next issue is the example allows a conversion of a number to a different value ie TEXT("4/15/91", "mmmm dd, yyyy")
which results in the date April 15, 1991

the text() function allows you to apply custom number formats to values. It returns a text string. If you want to use the date in further calculations, don't use the text() function, or at least don't refer to the results of the text() funciton in the calculations - use the original date.

Quote

What I want to do is bring the date in to the formula from another cell

as above - just use the original date value, not the text version.

HTH

paddy

Paddyd · Jun 5th 2003

=B1

somewhere else, or am I totally missing the point?

Paddyd · Jun 4th 2003

One, inelegant, method:

1) highlight the column, go to data | text to columns - hit next twice
2) select DMY as the column data format, hit finish.
3) 16:05 will now be held as 16/05/2000
4) convert this into values you want:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book5</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb663764><INPUT onclick='window.clipboardData.setData("Text",document.formFb685530.sltNb227554.value);' type=button value="Copy Formula" name=btCb592514></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb685530><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value" name=sltNb227554><OPTION value='=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)' selected>C2<OPTION value='=IF(ISNUMBER(A3),TEXT(DATE(YEAR(A3)+3,MONTH(A3),DAY(A3)),"ddd"),A3)'>C3<OPTION value='=IF(ISNUMBER(A4),TEXT(DATE(YEAR(A4)+3,MONTH(A4),DAY(A4)),"ddd"),A4)'>C4<OPTION value='=IF(ISNUMBER(A5),TEXT(DATE(YEAR(A5)+3,MONTH(A5),DAY(A5)),"ddd"),A5)'>C5<OPTION value='=IF(ISNUMBER(A6),TEXT(DATE(YEAR(A6)+3,MONTH(A6),DAY(A6)),"ddd"),A6)'>C6</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value='=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)' name=txbFb761501></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Day</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Day</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Derived</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[0].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">M</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">M</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[1].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">M</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">16/05/2000</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">F</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[2].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">Fri</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">15/05/2000</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">Th</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[3].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">Thu</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">W</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial">W</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"><A onclick="document.formFb685530.sltNb227554.options[4].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb227554.value;" href="#javascript:void(0);">W</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

the formula is:

=IF(ISNUMBER(A2),TEXT(DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),"ddd"),A2)

Posts by Paddyd

Formulas: converting numbers (amounts) into Words

Conditional Cell Change Value

Formulas: # of cells in a formula

Formulas: More than 7 IF statements - what should I do?

Formulas: HELP! Analysing telephone usage and difference bet

A question about my posts :(

Formulas: Need help regarding classification of data

[Solved] Display a picture according to cell value?

[Solved] Formulas: DIFFERENCE BETWEEN A DATE & TODAY DAT

Formulas: When IF functions don't scale anymore ?

Formulas: Sorting data tables by more than one colu

[Solved] SumProduct Query

[Solved] Formulas: Combine First, Middle and Last name from

[Solved] VBA: Convert decimal into time

[Solved] Joining text and values

[Solved] Joining text and values

[Solved] Joining text and values

[Solved] Joining text and values

[Solved] Get the value of =sum(A1:A10)

[Solved] VBA: Convert Date into Text