Formula Assistance

[ChillingSabrina]

Hi, Newbie here!

I've built a spreadsheet to track reports that are on track/due or overdue. I've added IF formulas to show the on track/due/overdue items and used conditional formatting to change the cell colour which is working without any problems.

I have chart to show the volume of items under each heading which is also working until I need to close a completed item.

The chart is still showing items that are overdue even though they are completed. I'm trying to get cell B5 and D5 to change to complete when a date is input into E5 but I can't get anything to work

[TABLE="width: 351"]
<tbody>[TR]
[TD="width: 73, bgcolor: white"]Two Weeks
[/TD]
[TD="width: 73, bgcolor: white"][/TD]
[TD="width: 79, bgcolor: white"]Four Weeks
[/TD]
[TD="width: 79, bgcolor: white"][/TD]
[TD="width: 98, bgcolor: white"]Completed Date
[/TD]
[TD="width: 65, bgcolor: #0070C0"][/TD]
[/TR]
[TR]
[TD="width: 73, bgcolor: black"][/TD]
[TD="width: 73, bgcolor: black"][/TD]
[TD="width: 79, bgcolor: black"][/TD]
[TD="width: 79, bgcolor: black"][/TD]
[TD="width: 98, bgcolor: black"]Completed Date
[/TD]
[TD="width: 65, bgcolor: black"][/TD]
[/TR]
[TR]
[TD="width: 73, bgcolor: transparent, align: right"]12/08/2019
[/TD]
[TD="width: 73, bgcolor: transparent"]On Track
[/TD]
[TD="width: 79, bgcolor: transparent, align: right"]27/08/2019
[/TD]
[TD="width: 79, bgcolor: transparent"]On Track
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]FALSE
[/TD]
[/TR]
[TR]
[TD="width: 73, align: right"]16/07/2019
[/TD]
[TD="width: 73"]Overdue
[/TD]
[TD="width: 79, align: right"]30/07/2019
[/TD]
[TD="width: 79"]Due
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]FALSE
[/TD]
[/TR]
[TR]
[TD="width: 73, align: right"]14/06/2019
[/TD]
[TD="width: 73"]Overdue
[/TD]
[TD="width: 79, align: right"]28/06/2019
[/TD]
[TD="width: 79"]Overdue
[/TD]
[TD="width: 98, bgcolor: transparent, align: right"]30/07/2019
[/TD]
[TD="width: 65, bgcolor: transparent"]TRUE
[/TD]
[/TR]
[TR]
[TD="width: 73, align: right"]15/07/2019
[/TD]
[TD="width: 73"]Overdue
[/TD]
[TD="width: 79, align: right"]29/07/2019
[/TD]
[TD="width: 79"]Overdue
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone help before I go crazy lol!

Thanks

CS

 

[hrayani]

Hi CS,

What formula are you using in Columns B & D to get due or Overdue?

 

[ChillingSabrina]

Hi,

The columns have changed but I'm using =IF($N4>TODAY(),"On Track",IF($N4=TODAY(),"Due",IF($N4<TODAY(),"Overdue"))) It's the same for the 4 week column except it's $P4

 

[ChillingSabrina]

Hi,<today(),"overdue"))) it's="" the="" same="" for="" 4="" week="" column="" except="" $p4[="" quote]


The columns have changed but I'm using =IF($N4>TODAY(),"On Track",IF($N4=TODAY(),"Due",IF($N4

Sorry, that doesn't seem to have posted in full - should be

=IF($N4>TODAY(),"On Track",IF($N4=TODAY(),"Due",IF($N4<TODAY(),"OVERDUE")))
</today(),"overdue")))>

 

[Peter_SSs]

Welcome to the MrExcel board!

The problem you are having with showing your formula occurs when a letter follows immediately after a < sign. The simplest solution is to insert a space between. The formula you were trying to post was
=IF($N4>TODAY(),"On Track",IF($N4=TODAY(),"Due",IF($N4< TODAY(),"Overdue")))

You haven't said exactly what columns you are now using, but see if this helps. I have assumed that the 'Completed Date' column is column R.

=IF($R4="",IF($N4>TODAY(),"On Track",IF($N4=TODAY(),"Due",IF($N4<TODAY(),"Overdue"))),"Complete")

 

[ChillingSabrina]

Hi,

Thanks - that's worked! Do you know what I could put in to remove the item from the active reports in a chart? I need to track the reports in a chart which is working for the on track/due/overdue items however they aren't moving from the active caseload when they are completed.

Thanks

CS

 

[Peter_SSs]

Hard to suggest without knowing exactly how your chart is set up, where its drawing its data from etc

 

[ChillingSabrina]

Hard to suggest without knowing exactly how your chart is set up, where its drawing its data from etc

I can't get a copy of it to post but the charts are drawing data from tables. I have a table with a counta drawing data from column L that counts the number of reports received and a counta drawing data from column R that shows the number of closed reports. I also need to track the number of reports that are at two weeks (on track, due, overdue) from column N and the same for 4 weeks from column P.

The problem I'm having is that the two week and four week reports are still showing in the table even when the report has been received and the item is completed. I've tried everything I can think of but I can't get column N and P to change to complete once the report is closed.

CS

 

[Peter_SSs]

Hard to advise as there isn't enough information for me to model what you have to test. I assume that it is the chart that you cannot post but can't you post the relevant table(s) (like you did in post 1) that the chart is using and explain the problem with the table? If you do post any table data, please ensure that we know what formulas, if any, are in the columns and identify what the columns in the table are.

My signature block below has a link for good ways to post small sample screen shots, including formulas, column letters etc (but not charts )