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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
912
Office Version
2016
Platform
Windows
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
45,255
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
45,255
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
45,255
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 :))
 

Watch MrExcel Video

Forum statistics

Threads
1,102,777
Messages
5,488,784
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top