4 Replies Latest reply: May 15, 2014 9:52 AM by Nick Osdale-Popa

# One Ugly Formula

My data is laid out like so:

[font="courier"][*Pertinate Employee Data/Job Worked/etc*]

SAT     SUN     MON     TUE     WED     THR     FRI

R:    0.00    0.00    8.00    8.00    0.00    0.00    0.00

O:    8.00    0.00    2.00    2.00    0.00    0.00    0.00

D:    0.00   10.00    0.00    0.00    0.00    0.00    0.00[/font][/quote]I have 2 line detail template as such:

Highlight the line with Sat~Fri and the first row of hours.

Trap on SAT[/b]

Capture the Line R: and all hours[/b] as one field

In advance, End Line on Blank Field Values [/b]

The reason for this is that I want to capture all hours in one line, rather than have 3 seperate lines. Also, some lines will have any combination of R, O, and D with a minimum of any one line and a maximum of all 3 lines.

Next I needed to break out the individual types of hours (R,O,D[/b]), deliminate them (;[/b]), remove any extra spaces, and last but not least, move the SAT[/b] and SUN[/b] hours to the end of the string.

Here's the formula that I used to accomplish that:

(I like the fact that you can space out formulas like this for easy reading/editing)

[font="courier"]Strip(

If(Instr(&quot;R:&quot;,Hours)&gt;0,

Substr(

Stuff(

Stuff(

Stuff(

Stuff(

Stuff(

Stuff(

SubStr(Hours,Instr(&quot;R:&quot;,Hours)+2,56)

,49,1,&quot;;&quot;)

,41,1,&quot;;&quot;)

,33,1,&quot;;&quot;)

,25,1,&quot;;&quot;)

,17,1,&quot;;&quot;)

,09,1,&quot;;&quot;),18,56)&quot;;&quot;

Left(

Stuff(

Stuff(

Stuff(

Stuff(

Stuff(

Stuff(

SubStr(Hours,Instr(&quot;R:&quot;,Hours)+2,56)

,49,1,&quot;;&quot;)

,41,1,&quot;;&quot;)

,33,1,&quot;;&quot;)

,25,1,&quot;;&quot;)

,17,1,&quot;;&quot;)

,09,1,&quot;;&quot;),16)

,&quot;0.00;0.00;0.00;0.00;0.00;0.00;0.00&quot;),&quot; &quot;)[/font][/quote]The last part of the formula was to return a string of Zero hours, if that particular Hours type was not found.

This formula was duplicated two more times change the R: to O: and D: where needed.

Granted, I could have broken this out by making the Stuff() formulas as a seperate field, but that's just cheating.

• ###### One Ugly Formula

Grant,

I can't answer your PM because your Inbox is full.

• ###### One Ugly Formula

Originally posted by Nick Osdale-Popa:

Grant,

I can't answer your PM because your Inbox is full.     /b[/quote]Darn - I'm sure it told me last time that happened ...

OK. I've made some space.

Thanks for letting me know.

Grant

• ###### One Ugly Formula

Looking at the new functions for Monarch 8 (I don't have my version yet!), this formula can be seriously improved with:

InTrim()

Replace()[/b]

If someone has V8 to try this, I'd like to see the new formula.    smile.gif[/img]

EDIT: I was thinking more about this and probably can do this without using the Replace()[/b] function.

[size="1"][ April 13, 2005, 09:23 PM: Message edited by: Nick Osdale-Popa ][/size]

• ###### One Ugly Formula

Here's what I came up with using the New V8 Formulas:

[font="courier"]Replace(

InTrim(

If(

Instr( &quot;R:&quot;, Hours ) &gt; 0,

SubStr( SubStr( Hours, Instr( &quot;R:&quot;, Hours ) + 3, 56 ), 18, 56 )

+ &quot; &quot; +

Left( SubStr( Hours, Instr( &quot;R:&quot;, Hours ) + 3, 56 ), 16),

&quot;0.00;0.00;0.00;0.00;0.00;0.00;0.00&quot;)

)

,&quot; &quot;,&quot;;&quot;)[/font][/quote]