I'd put the formula in a cell and change tools|options|general tab|and check
R1C1 Reference style.

(or use ConvertFormula in code)

Pete_UK wrote:

Well, Dave, would you like to translate that into R1C1 notation? <bg

Pete

Dave Peterson wrote:
The limit is 1024 characters when measured in R1C1 notation--not 255.

Pete_UK wrote:

You are limited to 255 characters in a formula - you have 365 in your
final formula, so you'll have to approach it differently. Can you split
your formula and make use of helper cells? It strikes me that a UDF
might be a better approach here.

Hope this helps.

Pete

wahiggin wrote:
I am trying to combined a couple of formulas from posts that I received
and keep getting an error message. NETWORKDAYS doesn't work by itself
because I have one extra work day, Saturday. So I was trying to
combine this:

=NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7)

and this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

A1 = Start Time
B1 = End Time
A2 = Task Start Date/Time
B2 = Task End Date/Time
D range = Holidays (my exmaple only had three holidays so it was
D1:D3)

To make this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

But I keep getting an error message.

Any ideas?


--
wahiggin
------------------------------------------------------------------------
wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013
View this thread: http://www.excelforum.com/showthread...hreadid=547509

--

Dave Peterson

--

Dave Peterson