=NETWORKDAYS where a date isn't included due to original report

Hi

I have to measure two criteria referral to assessment , assessment to delivery

I have used referencing in order to do this thanks to the advice on this forum.

Referral to Assessment

Formula:

Please Login or Register  to view this content.

eg. REFERRAL 22/07/15 ASSESSMENT 22/07/15

**this returns a figure of 1


Assessment to Delivery

Formula:

Please Login or Register  to view this content.

eg. ASSESSMENT 22/07.15 DELIVERY (BLANK)

**this returns a figure of -30143 due to the blank in delivery date,the ones with a delivery date seem to work but as some items haven't been delivered I think I'd like it to return a '0'


Both these formulas also use a holiday range. However I have a couple of issues with the results returned.

If there is no date in Delivery on the original sheet it assumes the date is 00/01/1900 which when calculated in the above formulas returns a value of -30143

I really hope I've explained this properly.

Thanks

Ben

What would you like it to return if the delivery date is blank?

Ideally a 0 or nothing at all.

I am not entirely sure which cell your delivery date is in but here goes:

=IF(ISBLANK(Delivery date here),"",NETWORKDAYS('[WHEELCHAIRS MONTHLY REPORT JULY 15.xlt]boo824qy'!$C11,'[WHEELCHAIRS MONTHLY REPORT JULY 15.xlt]boo824qy'!$D11,'C:\Documents and Settings\ben.healy\Desktop\SERVICE REPORT\[HOLIDAY.xls]Sheet1'!$D$2:$D$10))

Alternatively, you can check if there is a date(number) in the cell like so:

IF(ISNUMBER(Delivery date here),NETWORKDAYS('[WHEELCHAIRS MONTHLY REPORT JULY 15.xlt]boo824qy'!$C11,'[WHEELCHAIRS MONTHLY REPORT JULY 15.xlt]boo824qy'!$D11,'C:\Documents and Settings\ben.healy\Desktop\SERVICE REPORT\[HOLIDAY.xls]Sheet1'!$D$2:$D$10),"")

Any of these 2 should work.

Do the test in your NetworkDays function, not the cell with the delivery date.
If you make the cell with your delivery date = "", then the NetworkDays function will return #Value! Error.

Sinon posted perfectly good syntax for that.
Where that formula shows "Delivery date here", replace that with the cell reference holding the delivery date.

The ISBLANK() looks in a cell to see if there is anything in it. This usually works unless there is a space or the result from a formula which contains "". That is why I offered the second formula which looks in the cell and if it finds a date (a date is basically a number in excel) then it proceeds with the NETWORKDAYS formula. Otherwise it returns nothing. If you want to change the way the formula works please provide a sample and expected results (input manually) so as to better understand what you need.

Hi, I've had a play and tidied up a sample sheet, and shown the three methods discussed. Couple of issues, then again i may have forgotten something.

DATASETSSAMPLE.xls

Basically, if a field is empty eg B(REF),C(***) or D(DEL). then E (REF TO ***) or F (*** TO DEL) needs to reflect this.

F16 is showing "BLANK" due to the delivery being before the assessment. Meaning MAX formula has probably worked the best. As ISBLANK shows a figure of -30136 due to no del date, and ISNUMBER hasnt shown anything in F.

Thanks for your time

Ben

Your ISNUMBER and ISBLANK functions aren't working as expected because your 'blanks' in column D are NOT really blank.
They actually contain 0's
You just have 0's hidden in Options - Advanced, unchecked "Show a zero in cells with a zero value"
Select one of those blanks in D, and look at the Formula Bar. It shows 1/0/1900, which is 0 formatted as a date.


Also the true/false results of those 2 formulas should be reversed of each other
For ISBLANK, it's
=IF(ISBLANK(cell),"",NETWORKDAYS(...))

For ISNUMBER, it's
=IF(ISNUMBER(cell),NETWORKDAYS(...),"")

You have it backwards in the ISNUMBER formula.


But neither of those will work anyway because the cells actually contain zero's.
so ISBLANK will be FALSE, and ISNUMBER will be TRUE.


To resolve, Either.
1) Remove the 0's so the cells are truely empty
2) Try =IF(cell=0,"",NETWORKDAYS(...))