View original (French)

Excel table calculating number of working days negative number

Solved
Mathildeeeeeeee Posted messages 5 Status Member -  
Mathildeeeeeeee Posted messages 5 Status Member -
Hello, after searching through forums and Office support, I can't find the answer to my question. I have a table on which I need to calculate the number of working days from the submission of a document until its return. If I don't have the return date yet, it shows me a negative number. Additionally, with my formula =IF(OR(D3="",TODAY()=""),"",NETWORKDAYS(D3,H3))
I have an extra day. For example, if the submission is on 01/02 and the return is on 07/02, I have a processing time of 5 days when in reality it is 4 working days.
I would also like to apply conditional formatting to highlight in red if the delay exceeds 8 days, but when I do, and if I extend my formula, the empty fields turn red.

1st question: Why do I have an extra day in my formula?
2nd question: How can I avoid having negative numbers if I don't have the return date (can I have the number of days that elapse as time goes by)?
3rd question: I have the same formulas in the two columns on the right, yet in the last one, I have nothing at all even though I have also entered a submission date?
4th question: How can I do the conditional formatting without everything being red?

I am completely lost, and I will soon be pulling my hair out.. Thank you in advance, you will see a photo of my table, I hope it can help you understand..

2 answers

  1.
    Mathildeeeeeeee Posted messages 5 Status Member 1
     
    Well, I just realized a mistake, I used column H instead of D at the beginning of my formula and the negative numbers disappeared. However, I still can’t figure out the other questions..
    1
    1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      One more day?
      No, the working days are the 1st, 2nd, 5th, 6th, and 7th of February 2018, which makes 5 days (from the 1st of February to the 1st of February, there is one working day).
      0
    2. Mathildeeeeeeee Posted messages 5 Status Member 1
       
      I understand, but how can I do in this case? Because we agree that if I submit a document on 01/02 and I get a response on 07/02, logically the file took 4 days to be processed.
      0
    3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      if you don't want to count the last day, then you do -1
      eric
      0
    4. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      “Because we agree that if I submit a document on 02/01 and receive a response on 02/07, logically the file took 4 days to be processed.”
      Not necessarily, if you submit it on 02/01 at the start of the day and retrieve it on 02/07 at the end of the day, there are indeed 5 working days!
      On the principle that any day started is owed.
      0
    5. Mathildeeeeeeee Posted messages 5 Status Member 1
       
      Yes, that's true, thank you all!
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    A) In your formula, writing the condition TODAY()="" is unnecessary since TODAY() always returns the current date and never anything else!

    B) If you do not want to include the limits in the time frame, you can simply adjust the formula by -1 (just like with intervals), that is:
    to prevent the formula from returning nonsense if one of the dates is missing
    =IF(OR(D3="",H3=""),"",NETWORKDAYS(D3,H3)-1)

    C) Modification deadline: only line 20 can return a result, it is the only one in the image that displays modification dates

    E) You do not specify what you want to highlight in red, but if it is to mark the date in F in red if it is overdue and there is no date in H using conditional formatting by formula, the formula is:

    =AND($F3<>"",$H3="",$F3<TODAY())

    so, be sure to specify F3<>"" to avoid displaying empty cells in red since an empty F is <TODAY()

    F) As additional information, with the NETWORKDAYS formula, you can also eliminate holidays
    for example, if you make a list of days to exclude in Z1:Z20

    =NETWORKDAYS(D3,H3,$Z$1:$Z$20) will eliminate the dates from Z1 to Z20 (and only once if it is a Sunday or Saturday)

    Please get back to me if this doesn’t work

    Best regards

    0
    1. Mathildeeeeeeee Posted messages 5 Status Member 1
       
      Thank you very much!
      0