simplify this formula

Here's the formula:

=IF(B6<DATE(2006,6,1),IF(G6<=40,G6*35,(40*35+((G6-40)*35*1.5))),IF(G6<=40,G6*$F$2,(40*37+((G6-40)*$F$2*1.5))))

This formula calculates correctly, so that's not the issue. I'm wondering
if there is a clearer (more concise) way to accomplish the calculation I'm
trying to do, without using helper columns.

Here's the scenario: for dates prior to 6/1/2006 the client's billing rate
is $35/hr, with 1.5x for any hours over 40 hrs per week, and, for all dates
after 6/1/2006 the client's billing rate is $37 per hour with 1.5x for any
hours over 40 hours per week.

Any ideas?

Hi,

What's in F2 ??

VBA Noob

How about this

=(MIN(G6,40)+MAX(0,G6-40)*1.5)*(IF(B6<DATE(2006,6,1),35,37))


or

=(G6+MAX(0,G6-40)*0.5)*(IF(B6<DATE(2006,6,1),35,37))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Dave F" <DaveF@discussions.microsoft.com> wrote in message
news:F87FB881-8C23-488E-B630-A483B823FB99@microsoft.com...
> Here's the formula:
>
>
=IF(B6<DATE(2006,6,1),IF(G6<=40,G6*35,(40*35+((G6-40)*35*1.5))),IF(G6<=40,G6
*$F$2,(40*37+((G6-40)*$F$2*1.5))))
>
> This formula calculates correctly, so that's not the issue. I'm wondering
> if there is a clearer (more concise) way to accomplish the calculation I'm
> trying to do, without using helper columns.
>
> Here's the scenario: for dates prior to 6/1/2006 the client's billing rate
> is $35/hr, with 1.5x for any hours over 40 hrs per week, and, for all
dates
> after 6/1/2006 the client's billing rate is $37 per hour with 1.5x for any
> hours over 40 hours per week.
>
> Any ideas?

Wow, Bob, that's impressive.

Will have to try it out ,thanks.

Dave

"Bob Phillips" wrote:

> How about this
>
> =(MIN(G6,40)+MAX(0,G6-40)*1.5)*(IF(B6<DATE(2006,6,1),35,37))
>
>
> or
>
> =(G6+MAX(0,G6-40)*0.5)*(IF(B6<DATE(2006,6,1),35,37))
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Dave F" <DaveF@discussions.microsoft.com> wrote in message
> news:F87FB881-8C23-488E-B630-A483B823FB99@microsoft.com...
> > Here's the formula:
> >
> >
> =IF(B6<DATE(2006,6,1),IF(G6<=40,G6*35,(40*35+((G6-40)*35*1.5))),IF(G6<=40,G6
> *$F$2,(40*37+((G6-40)*$F$2*1.5))))
> >
> > This formula calculates correctly, so that's not the issue. I'm wondering
> > if there is a clearer (more concise) way to accomplish the calculation I'm
> > trying to do, without using helper columns.
> >
> > Here's the scenario: for dates prior to 6/1/2006 the client's billing rate
> > is $35/hr, with 1.5x for any hours over 40 hrs per week, and, for all
> dates
> > after 6/1/2006 the client's billing rate is $37 per hour with 1.5x for any
> > hours over 40 hours per week.
> >
> > Any ideas?
>
>
>

Column F has the hours worked for the week, sorry.

I think Bob came up with the solution.

Dave

"VBA Noob" wrote:

>
> Hi,
>
> What's in F2 ??
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=569196
>
>

Totally IFless

=(G6+MAX(0,G6-40)*0.5)*(35+(B6>=DATE(2006,6,1))*2)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Dave F" <DaveF@discussions.microsoft.com> wrote in message
news:C7686F83-E19D-46B6-8A8E-C3806A29CB84@microsoft.com...
> Wow, Bob, that's impressive.
>
> Will have to try it out ,thanks.
>
> Dave
>
> "Bob Phillips" wrote:
>
> > How about this
> >
> > =(MIN(G6,40)+MAX(0,G6-40)*1.5)*(IF(B6<DATE(2006,6,1),35,37))
> >
> >
> > or
> >
> > =(G6+MAX(0,G6-40)*0.5)*(IF(B6<DATE(2006,6,1),35,37))
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Dave F" <DaveF@discussions.microsoft.com> wrote in message
> > news:F87FB881-8C23-488E-B630-A483B823FB99@microsoft.com...
> > > Here's the formula:
> > >
> > >
> >
=IF(B6<DATE(2006,6,1),IF(G6<=40,G6*35,(40*35+((G6-40)*35*1.5))),IF(G6<=40,G6
> > *$F$2,(40*37+((G6-40)*$F$2*1.5))))
> > >
> > > This formula calculates correctly, so that's not the issue. I'm
wondering
> > > if there is a clearer (more concise) way to accomplish the calculation
I'm
> > > trying to do, without using helper columns.
> > >
> > > Here's the scenario: for dates prior to 6/1/2006 the client's billing
rate
> > > is $35/hr, with 1.5x for any hours over 40 hrs per week, and, for all
> > dates
> > > after 6/1/2006 the client's billing rate is $37 per hour with 1.5x for
any
> > > hours over 40 hours per week.
> > >
> > > Any ideas?
> >
> >
> >