Trying to combine If statements

[dezibluenose]

Hi All - again I'm showing my lack of excel skills here and hoping someone can help me out.

I'm trying to complete a report for my work and show the days between an audit start and close date - easy enough to do and in column C of the picture below I put the following:-

=IF(B2="","",(DAYS(B2,A2))) As you can see, it give me what I need - shows the days between the start and end of the audit, and if the audit is not closed it shows as blank.


Where my problems start is when I've asked to do the same thing for audits that have not been closed yet. My formula works fine until it comes across an audit that's not been started yet.

I tried this formula =IF(B2="",TODAY()-A2,C2) - saying if the audit is not closed, show me the days it's been open for, and if it is closed, refer to the answer in the previous formula. Again that works fine unless the audit is not started yet and it's blank for audit start date. When that happens I get a massive wrong number.

I was trying to combine an IF and an OR statement together but started getting all sorts of wrong numbers. This will probably be something simple but how would you fix it so that if an audit hasn't been started yet I just see a blank entry, but retain the formula showing the open number of days?

Example Sheet

Formulas Used

 

[etaf]

so if the audit has not started then

=IF( A2 = "", "", IF(
now we need to add the other formula
IF(B2="",TODAY()-A2,C2
and if A2 and B2 has a date
(DAYS(B2,A2)))

so
=IF( A2 = "", "", IF(B2="",TODAY()-A2,DAYS(B2,A2) ))
or
=IF( A2 = "", "", IF(B2="",TODAY()-A2,B2-A2 ))

Book5
	A	B	C
1	strat	end	days
2
3	1/1/24	1/2/24	1
4	1/1/24		78
5
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF( A2 = "", "", IF(B2="",TODAY()-A2,B2-A2 ))
C5	C5	=IF( A5 = "", "", IF(B5="",TODAY()-A5,DAYS(B5,A5) ))

 

[dezibluenose]

so if the audit has not started then

=IF( A2 = "", "", IF(
now we need to add the other formula
IF(B2="",TODAY()-A2,C2
and if A2 and B2 has a date
(DAYS(B2,A2)))

so
=IF( A2 = "", "", IF(B2="",TODAY()-A2,DAYS(B2,A2) ))
or
=IF( A2 = "", "", IF(B2="",TODAY()-A2,B2-A2 ))

Book5
	A	B	C
1	strat	end	days
2
3	1/1/24	1/2/24	1
4	1/1/24		78
5
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF( A2 = "", "", IF(B2="",TODAY()-A2,B2-A2 ))
C5	C5	=IF( A5 = "", "", IF(B5="",TODAY()-A5,DAYS(B5,A5) ))

Fantastic etaf - thank you so much. That works perfectly.

Now as usual I need to break down everything you did for the future. Thanks again.

 

[etaf]

you are welcome

Nested IF
IF( test, result if true , result if false )
so you can replace the true or the false with another if statement

IF( test, result if true , IF( test, result if true , IF( test, result if true , result if false ) ) )
it works in left to right Order
maybe that helps a little
otherwise - look for info on NESTED IF