Help with Calculated Column Nested IF Statements

[Anonymous]

I have an IF statement that is partially correct... 

Here is the current function: 

Expired in Middle = IF(ERT_CHANGE_LOG[USERNAME] = "system", -1, (([TIME_OF_EVENT]-[PREV_ERT])*1440))

But, there is a caveat to it. Sometimes the [PREV_ERT] column contains blanks, and if that column is blank, I want the statement to then to look at the [ERT] column and do the function: [TIME_OF_EVENT]-[ERT])*1440

Any help with the syntax would be greatly appreciated!

I have attached a picture of the table in the data view below: 

[jppv20 · Accepted solution]

Hi @Anonymous ,

This should work:

Expired in Middle = 
IF(ERT_CHANGE_LOG[USERNAME] = "system", -1,
IF([PREV_ERT]=BLANK(),([TIME_OF_EVENT]-[ERT])*1440,
([TIME_OF_EVENT]-[PREV_ERT])*1440))

If I answered your question, please mark it as a solution to help other members find it more quickly.

[Anonymous]

Yeah that is exactly what I need. Thanks!

[smpa01]

@Anonymous  can you please post some sample data

[Anonymous]

Here is a picture of sample data: 

[smpa01]

@Anonymous 

IF(ERT_CHANGE_LOG[USERNAME] = "system", -1, (([TIME_OF_EVENT]-IF([PREV_ERT]=BLANK(),[ERT],[PREV_ERT])*1440)))

[Anonymous]

I think that is close but not quite it. It was giving me some crazy values when I input that function in.