Workday formula - adjust for a zero occurrence

[HWillis]

Hi there,

I have a formula that calculates the KPI days from the date deemed adequate.

=IFERROR(WORKDAY([@[Date Allocated or Adequate]],[@[DPE KPI(working days)]],Holidays),"")............ returning the DPE KPI Target Date.

If the KPI working days (returned from a vlookup) happen to be zero I would like the DPE KPI Target Date column to be blank.

Is there a way I can alter the formula above to do this??

Many thanks, H.

 

[RasGhul]

Hi,

You can turn off zero's in File/Options/Advanced - Scroll down to "Display options for this Sheet" - Un-tick "Show a zero in cells that have a zero value".

 

[HWillis]

Hi,

Thanks for your reply.

I am happy for the 0 to show - it's the date in the last column I would like to show as blank. It currently shows as the date @[Date Allocated or Adequate] as there are zero days added. This get confusing to users of the spreadsheet.

You may notice that the 'Northern KPI (days)' showing 7 - calculates to 9/9/2019 as I require.

Cheers, H.

 

[RasGhul]

Ok try,

Northern KPI Days calculates to zero so you could use that;

=IF(Northern KPI Days=0,"",IFERROR(WORKDAY([@[Date Allocated or Adequate]],[@[DPE KPI(working days)]],Holidays),""))

 

[HWillis]

Awesome - thank you RasGhul



=IF([DPE KPI (working days)]=0,"",IFERROR(WORKDAY([@[Date Allocated or Adequate]],[@[DPE KPI (working days)]],Holidays),""))