Multiple conditions and arithmetic for one cell

[destiny4adams]

The formula I am using is not calculating correctly. What am I doing wrong?

=IF(C5=0,"",IF(C5>0,SUM(C5-20),IF(A5="*Nov*",A5="*Dec*",A5="*Jan*",A5="*Feb*",SUM(C5-98))))

What I am looking for is, if cell C5=0 then leave blank, if cell C5>0 then subtract 20 from cell C5, and if cell A5 contains Nov subtract 98 from C5.

The results I am looking for are:
I need cell G (How many days over) to calculate how many days Shawn worked over his contract days, which is, 20 days at work, 10 off, except during Nov-Feb then it is 98 days at work, 0 days off.

I need cell O (How many days under) to calculate how many days less than Shawn's contract is for being off work.

I tried to open the XL2BB, so I could upload my sheet, but I keep getting an error saying it cannot open in protected view (my workbook is not protected).

 

[Peter_SSs]

Welcome to the MrExcel board!

I tried to open the XL2BB, so I could upload my sheet, but I keep getting an error saying it cannot open in protected view

See if this helps XL2BB Icons greyed out

and if cell A5 contains Nov subtract 98 from C5.

So what are *Dec*, *Jan* etc in the formula for?

Are the dates in column A actual dates or text? In a blank cell, what does this formula return? =ISNUMBER(A5)

Are the values shown in "How many days over" in your picture the results you want for that sample data?

 

[destiny4adams]

I was told that * was a wild card and if you don't know where in the reference cell the information will appear then you are supposed to put * before and after the word you are looking for.

 

[destiny4adams]

I was told that * was a wild card and if you don't know where in the reference cell the information will appear then you are supposed to put * before and after the word you are looking for.

Yes the dates are actual dates, that is just a format that I chose

 

[Peter_SSs]

Thanks.
.. and my final question?

Are the values shown in "How many days over" in your picture the results you want for that sample data?

 

[destiny4adams]

Thanks.
.. and my final question?

Yes, those are what they should be. I manually calculated them so they are examples of what I want excel to do automatically.

 

[Peter_SSs]

Thanks. Try the formula below.

BTW, did you try my suggestion about XL2BB? You will generally get much faster/better responses if you can provide your sample data that way.

23 07 05.xlsm
	A		C				G
5	18-Sep-2022		43				23
6	13-Nov-2022		146				48
7	16-Apr-2023		28				8
8	28-May-2023		24				4
9	02-Jul-2023		27				7
10
Days over

Cell Formulas
Range	Formula
G5:G10	G5	=IF(A5="","",C5-IF(OR(MONTH(A5)={11,12,1,2}),98,20))

 

[destiny4adams]

Thanks. Try the formula below.

BTW, did you try my suggestion about XL2BB? You will generally get much faster/better responses if you can provide your sample data that way.

23 07 05.xlsm
	A		C				G
5	18-Sep-2022		43				23
6	13-Nov-2022		146				48
7	16-Apr-2023		28				8
8	28-May-2023		24				4
9	02-Jul-2023		27				7
10
Days over

Cell Formulas
Range	Formula
G5:G10	G5	=IF(A5="","",C5-IF(OR(MONTH(A5)={11,12,1,2}),98,20))

I did try and it still would not work. I followed the instructions and I now have the button on the ribbon, but it doesn't work.

 

[destiny4adams]

Thanks. Try the formula below.

BTW, did you try my suggestion about XL2BB? You will generally get much faster/better responses if you can provide your sample data that way.

23 07 05.xlsm
	A		C				G
5	18-Sep-2022		43				23
6	13-Nov-2022		146				48
7	16-Apr-2023		28				8
8	28-May-2023		24				4
9	02-Jul-2023		27				7
10
Days over

Cell Formulas
Range	Formula
G5:G10	G5	=IF(A5="","",C5-IF(OR(MONTH(A5)={11,12,1,2}),98,20))

Thank you so much that is exactly what I needed! You are such a good help, Peter!

 

[Peter_SSs]

.. but it doesn't work.

• What are the symptoms of "it doesn't work"?
• Are the buttons greyed out like in the link I provided or are they 'solid' like this?
  

Thank you so much that is exactly what I needed!

You're welcome. Thanks for the confirmation.