Formula Assistance

ChillingSabrina

New Member
Joined
Jul 30, 2019
Messages
5
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

Two Weeks
Four Weeks
Completed Date
Completed Date
12/08/2019
On Track
27/08/2019
On Track
FALSE
16/07/2019
Overdue
30/07/2019
Due
FALSE
14/06/2019
Overdue
28/06/2019
Overdue
30/07/2019
TRUE
15/07/2019
Overdue
29/07/2019
Overdue
FALSE

<tbody>
</tbody>


Can anyone help before I go crazy lol!

Thanks

CS
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
764
Hi CS,

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

ChillingSabrina

New Member
Joined
Jul 30, 2019
Messages
5
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

New Member
Joined
Jul 30, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 30, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
Hard to suggest without knowing exactly how your chart is set up, where its drawing its data from etc
 

ChillingSabrina

New Member
Joined
Jul 30, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
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 :))
 

Forum statistics

Threads
1,082,137
Messages
5,363,349
Members
400,729
Latest member
Lisa McConachy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top