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?

simplify this formula
 

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

simplify this formula
 

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" wrote in message
...
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?

simplify this formula
 

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" wrote in message
...
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?

simplify this formula
 

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

simplify this formula
 

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" wrote in message
...
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" wrote in message
...
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?