If cell is blank

Samuel Dowdy Jr.

=IF([Audit Due]@row < TODAY(), 1, 0)

if the Audit due is blank I want the the answer to be 0. How would I write this formula?

Protonsponge

Hello @Samuel Dowdy Jr.

Could the following be an option for you?

=IF(NOT(ISDATE([Audit Date]@row)), 0, IF([Audit Date]@row < TODAY(), 1, 0))

I hope that is helpful to you in some way,

Protonsponge

Protonsponge

Hello @Samuel Dowdy Jr.,

Are you looking to say if [Audit Status] is anything except "Closed" then 1, else if [Audit Date] is blank then 0, else if [Audit Date] is less than today then 1?

The first criteria (=IF([Audit Status]@row <> "Closed", 1) is going to assign 1 to anything that is not "Closed" and so I am wondering if I have misunderstood where you would like the [Audit Status] criteria.

=IF([Audit Status]@row <> "Closed", 1, IF(NOT(ISDATE([Audit Date]@row)), 0, IF([Audit Date]@row < TODAY(), 1, 0)))

Protonsponge

Hello @Samuel Dowdy Jr.

Could the following be an option for you?

=IF(NOT(ISDATE([Audit Date]@row)), 0, IF([Audit Date]@row < TODAY(), 1, 0))

I hope that is helpful to you in some way,

Protonsponge

Samuel Dowdy Jr.

Ok, I missed a step. I have a cell called Audit Status. I also need to add to the formula that if the status is <> Closed then the answer is 1

Darla Brown

Hi,

I would remove the 1 and the following comma. Your formula states, "If the Audit Due is less than today, return 1. That's what it will always do. But if you want the return to be 0, you don't need the 1.

Does this make sense?

Samuel Dowdy Jr.

@Protonsponge answer worked for my original question. Just need to add the status

Protonsponge

Hello @Samuel Dowdy Jr.,

Are you looking to say if [Audit Status] is anything except "Closed" then 1, else if [Audit Date] is blank then 0, else if [Audit Date] is less than today then 1?

The first criteria (=IF([Audit Status]@row <> "Closed", 1) is going to assign 1 to anything that is not "Closed" and so I am wondering if I have misunderstood where you would like the [Audit Status] criteria.

=IF([Audit Status]@row <> "Closed", 1, IF(NOT(ISDATE([Audit Date]@row)), 0, IF([Audit Date]@row < TODAY(), 1, 0)))

Samuel Dowdy Jr.

@Protonsponge, if the Audit date is blank. I need the answer to be 0

This is what I need:

audit is blank = 0

audit is <today = 0

audit stutus is closed = 0

How can I get the formula to do that?

Protonsponge

Hello @Samuel Dowdy Jr.

My apologies for misunderstanding, I think what you are looking for can be achieved with the following:-

=IF([Audit Status]@row = "Closed", 0, IF(NOT(ISDATE([Audit Due]@row)), 0, IF([Audit Due]@row < TODAY(), 1, 0)))

I am not 100% sure if you are looking to surface audits coming in the future or in the past. If you want to know how many Audits due in the future you might want this one instead:-

=IF([Audit Status]@row = "Closed", 0, IF(NOT(ISDATE([Audit Due]@row)), 0, IF([Audit Due]@row > TODAY(), 1, 0)))

Protonsponge