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

[colinheslop1984]

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

 

[hernantorres23]

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]

Full Year GDPR Rota - BLANK.xlsm

drive.google.com

 

[colinheslop1984]

Full Year GDPR Rota - BLANK.xlsm

drive.google.com

page requiring formula is on worksheet titled holiday

 

[colinheslop1984]

=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