3

I'm trying to write an IF formula to calculate 4 types of data based on 2 dates.

Example:

Cell I4 (Install Start)
Data entered here, eg 20/10/2015

Cell J4 (Install End)
Date entered here, eg 25/10/2015

Cell K4 (Days to Install)

  • a) show "Enter Start Date" if Cell I4 is empty
  • a) calculate how many days between today and install date
  • b) show "in progress" if install date is today
  • c) show "completed" if install date is prior to today
  • I have the following formula:

    =IF(I4=0,"Enter Start Date",IF(I4=TODAY(),"In Progress",IF(I4>TODAY(),I4-TODAY(),IF(J4>TODAY(),"Completed"))))

    Broken down:

  • Show Enter Start Date if start date is empty: IF(I4=0,"Enter Start Date",
  • Show In Progress if start date is today: IF(I4=TODAY(),"In Progress",
  • Show No of days between today and install date: IF(I4>TODAY(),I4-TODAY(),
  • Show Completed if end date is greater than start date: IF(J4>TODAY(),"Completed"))))
  • Excel accepts the formula and doesn't show any errors, the first 3 requirements work but the part which I want to show "Completed" doesn't work regardless if the end date is greater than the start date: IF(J4>TODAY(),"Completed"))))

    I may be approaching this all backwards, thank for your help!

    1 Answer 1

    2

    I think you are close but your formula is a little backwards. See my example below which should help you:

    enter image description here

    The formula I use is:

    =IF(A2="","Enter Start Date",IF(A2=TODAY(),"In Progress",IF(B2<TODAY(),"Completed",B2-A2)))
    

    It looks like you used a > instead of a < sign to show if it is completed.

    (For anyone using this in the future, today's date was 10/20/2015)

    5
    • Thank you for taking the time to respond. I have tried your formula and it all looks to work great except for calculating days until the install start. Instead of showing how many days to install it shows the difference between start date and end date E.G - with your formula I get a calculation of 4 when end date is 10/25/2015 and start date is it 10/21/15. What I was hoping for was a calculation of 1 when the start date is tomorrow. I hope that makes sense. Thanks Oct 20, 2015 at 14:26
    • Replace "B2-A2" with "A2-TODAY()" then it should work like that
      – Eric F
      Oct 20, 2015 at 14:28
    • 2
      Good answer, and the animated gif puts it over the top. :-)
      – fixer1234
      Oct 20, 2015 at 16:55
    • Hi @EricF, we have been using your formula for a couple of days, thank you. Just a further question. The formula with A2-TODAY() tells us how many days there are between TODAY and the END DATE with a minus sign if the START DATE and END DATE are in the future. We have tried to change this so it shows how many days are between TODAY and the START DATE only, but cannot work this out. Thanks again Oct 23, 2015 at 13:56
    • Just add another IF statement in the section that displays the A2-TODAY()
      – Eric F
      Oct 23, 2015 at 14:59

    You must log in to answer this question.

    Not the answer you're looking for? Browse other questions tagged .