Complete my formula...if function with multiple criteria (or alternative ideas)

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
109
Please can someone correct my formula, I feel like I am so close but struggling to get it right.

Below is as far as I have got but I just cant find the right expressions for it to work correctly. I have attached a picture of the document I am working with for your reference.

=IF(E8="",IF($C8<$C$2,$B8*$C$1,SUM($C$1-$F8)*$B8),(E8-F8)*B8)

I want to calculate YTD contracted hours with the following considerations

if start date is < c2 then multiply contracted hours by c1
if start date if > c2 then multiply contracted hours by value of c1-e8
if employment end date is inputted I want the calculation to stop beyond the date or week number inputted in either d8 or e8 respectively, e8 is preffered
If no employment end date is inputted, the calculation should continue to run

Thanks
 

Attachments

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi, we are pleased to help you, but please, put your data example, using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

In some cases, users are disabled for install an add-in. If this condition is applicable, share your file with Dropbox, Drive, etc.

Also, you can copy and paste your data, formatting until if look like your Workbook.
 

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
109
=IF($E9="",SUM(IF($C9<$C$2,$B9*$C$1,($C$1-$F9)*$B9)),SUM(IF($C9<$C$2,$E9*$B9,($E9-$F9)*$B9)))

I figured it out, solution above if anyone wanted to see it
 

Watch MrExcel Video

Forum statistics

Threads
1,100,136
Messages
5,472,736
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top