Excel help - Need to hide unfinished formula ?

  xphile 18:55 14 Aug 2006
Locked

I have devised an excel sheet to show date order received(col A1), date order complete (col B1) & a formula in col C1 to show number of days to complete the order. When I put a date in cell A1 I want C1 to remain blank until a date is entered in cell B1, as at the moment with only a date in A1, cell C1 returns a negative number. How can I leave it blank until both dates are entered in A1 & B1 ?

  VoG II 18:58 14 Aug 2006

Something like

=IF(NOT(ISBLANK(B1)),A1-B1,"")

  VoG II 18:59 14 Aug 2006

Actually

=IF(ISBLANK(B1),"", A1-B1)

is better.

  xphile 19:55 14 Aug 2006

I only want it to include weekdays Mon-Fri. My original formula FOR C1 was =NETWORKDAYS(A1,B1). Am now stuck incorporating the above into it. Comes up with 'too many arguments'. I'm a bit rubbish with excel.

  VoG II 20:03 14 Aug 2006

=IF(ISBLANK(B1),"",NETWORKDAYS(A1,B1))

  xphile 20:58 14 Aug 2006

have added an extra column(D), for Number of days outstanding at month end for uncleared orders. Have an extra cell G1 for the month end date to go in. Now have formula for col.D looking like this: =IF(ISBLANK(B1),NETWORKDAYS(A1,$G$1))

However while my column A is blank it is still filling in column D with values (I assume because I have the month end date filled in G1). How can I make this column stay empty until I have dates filled in column A? (seems to work if I remove the month end date from G1, but can I do it with G1 date still in?)

  xphile 21:02 14 Aug 2006

Need this new column D to stay blank if there IS a clearance date in column B.

  xphile 21:05 14 Aug 2006

=IF(ISBLANK(B1),NETWORKDAYS(A1,$G$2),"")

  VoG II 21:05 14 Aug 2006

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",NETWORKDAYS(A1,$G$1))

  xphile 21:20 14 Aug 2006

This formula seems to have done the trick for my last column:
=IF(ISBLANK(A6),"",NETWORKDAYS(A6,$G$2))
The other wasn't filling in my outstanding days because of the isblank B1 argument.Realised as long as col.A blank then only need that argument. Cheers for all help. Now finally looking like a working solution to a big statistical headache. Wil start a new thread when I want to add a macro! lol (Kidding)

  xphile 21:44 14 Aug 2006

marked as resolved too soon. With my formula above, it is entering a value in D when there is a value is in B (which i don't want). With VoG's previous formula was leaving D blank when date in A but not in B. What i want to happen is:-

If date in A but not B then want C blank(days to clear)& a value in days o/s in D.
If dates in A & B then want days to clear value in C but D to be blank.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?