Custom Column Formula Help

[bdehning]

I have the following formula for a custom column.  

It works except when Time Started or Time Injured is null and then my column returns an error

if([Time Started]>[Time Injured]) then [Time Injured]-[Time Started]+1 else [Time Injured]-[Time Started]

I would like to be to have the value be Unknown if possible?

[bdehning]

 if([Time Started]>[Time Injured]) then [Time Injured]-[Time Started]+1 else [Time Injured]-[Time Started]  

How do I add "if  time injured is null or time started is null then null" to what I already have?

[ryan_mayu · Accepted solution]

@bdehning 

pls try this

if [Time Started] is null or [Time Injured] is null then null else if [Time Started]>[Time Injured] then [Time Injured]-[Time Started]+1 else [Time Injured]-[Time Started]

[bdehning]

 Here is screen shot of Power Query   See my Time of Work Column which has the error?

[ryan_mayu]

@bdehning 

you can use  try...otherwise... to avoid error

Error handling - Power Query | Microsoft Learn

[bdehning]

Right,

So there is no easy add on to tell Custom Column it to produce "null" if one or both columns are null

[ryan_mayu]

The other way is 

if  time injured is null or time started is null then null 

i think "try" is easier.

[bdehning]

I do not need text until I create the Time to Injury Group Conditional Column  

[bdehning]

 I know that would be best but my report is so big and complicated, its hard to split out just the page and table I need for a sample.   

[ryan_mayu]

@bdehning 

not sure if this is what you want. We can't get text and number in one column

Column = if(ISBLANK('Table (2)'[Time Started]) || isblank('Table (2)'[Time Injured]),999,if('Table (2)'[Time Started]>'Table (2)'[Time Injured],'Table (2)'[Time Injured]-'Table (2)'[Time Started]+1,'Table (2)'[Time Injured]-'Table (2)'[Time Started]))

so i used 999 to replace the unknown

[ryan_mayu]

@bdehning 

could you pls provide some sample data?