Creating RAG Status Between Two Dates - IF / AND STATEMENT?
May 22, 2014
I am trying to generate a RAG status on an action log. Column K is the start date, Column L is the due/delivery date. I need a formula to bring through the following (i'll use conditional formatting to change "G" to green etc) -
show "R" if past due/delivery date
show "A" if past start date but before delivery date
show "G" if not past start date yet
This is probably really simple, but I've been trying various IF/AND statements and can get the "G" to work but not the rest for some reason. I think i may be using 'TODAY' incorrectly in my formula.
I've a table of historical members status list. with this table, I could track each member status history, since the beginning they become a member.
Here is the short of table
Name Member Status Date
Adel New Member 1-Jan-14
[Code] ....
I expect to use Pivot table to show how many people which are still "New Member" and/or "Junior Member" up to now. From the table above, there should be: only one person who still as New Member. because it is only Smith, and only two people with Junior Member. they are Adel and Jhon.
I've search around and found the following useful link, viewing only the last date in a pivot table for each user also A quick way to return the latest date in a subset in Excel
How to know the last status of each user (each member in my case).
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
I am working on creating a looped if statement but can not get it working.
I have 3 columns:
D3:D50 = Application ID G3:G50 = Sorted H3:H50 = PasteCreate
Column D contains unique records, however the first 6 characters could be the same so I have used the Left statement (=LEFT(D3,6)) to sort by this in column G.
Application ID
Sorted PasteCreate
ES0920.21
ES0920
[Code] .........
What I want it to do is to go through the list and Match the first 6 characters of cells in coloumn D:D create a new worksheet and name it the first 6 characters then copy (in the case above it would create 2 new worksheets that would be called ES0920 and ES1341.
ES0920 would have all the ES0920... names pasted into cells A2:R2 and ES1341 would have the 2 ES1341... names pasted into A2 and B2
The list of EStext will continually change. but will always contain ES followed by 6 or more numbers.
HOW I CAN USE IN EXCEL VALIDATION DROP DOWN BELOW CONDITION =IF(B1="Assets",[Mapping.xls]Assets!$A:$A,IF(B1="Liability",[Mapping.xls]Liability!$A:$A,""))
i have a invoice number column and date column and profit column for each job that they do. they may do several jobs in one day. what i want to know is how much they make total for the week. so in a separate column i want a weekending "such and such date" profit total
then i can have this for each person then i can just add up each persons week ending column to see how much i have made for this week. but i only what it to add up the numbers in a set range of dates.
for instance in the week ending 9/4/09 column it should only add profits that go from dates aug 30 to sept 4. and in weekending column 9/11/09 it should only add up profits corresponding to dates between 9/7/09 and 9/11/09
I was wondering if this can be done we have a monthly award program so if a date was entered for that drive on this month it would = no if blank =yes but needs to reset each month?
=IF(AND('Driver'!A51:A1000)="",IF('Driver'!A51:A1000>='Driver Bonus'!A3,"YES","NO")) This brings back a #value! error
=IF(AND('Driver'!A51<=A3,'Driver'!A51>=A3),"NO","YES") this works but only for that cell i need to also recognize cells A51:A1000
=IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<A3+1,IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<>A3,"YES","NO")) this also works but only for 1 day
I have data, which has an oil well that has had sections of its pipe perforated.
This has all happened on different days throughout the wells life.
I need an equation that says TRUE if the Perforation is the Most Recent for that well.
But I also need it to say True at different Top and Bottom Depth intervals throughout a specific well.
(for example: the last row in this data. Says TRUE even though there are more recent dates in this well.
This needs to say true because this is the most recent perforation in the top and bottom depth interval 9526 to 9536.
The other most recent perforations happened in shallower intervals, ie. 9456 to 9480.
So I need something in the equation that will say true to the most recent start date, if that top and bottom depth interval has not already been selected as true.
I need to have a column that says True IF the start date is the newest (most recent) for each individual well.
Easy enough.. But. I also need this to say True multiple times at different dates for each well.
For example below. it says true on 7/22/2001 three times and then True again on the last row on 10/14/1992.
It has to say true there because this is the most recent perforation that Top_Depth and Bottom_Depth interval.
WELLNAME TOP_DEPTH BOTTOM_DEPTH START DATE IF most recent Perf
We have a bunch of 8 digit Purchase Orders (POs) with end dates. We want to show all ten different PO's on a Line graph on the left with the end date at the bottom. Our issue seems to be that:
1. The graph doesn't show the exact PO #, but does a range. We need each PO number listed. How do we get the graph so show each individual PO on the left?
I have a spreadsheet that I'm trying to make a graph that shows the amount of letters mailed in a certain month.
The first line has 01/24/2008 as date received and 01/28/08 as date mailed. I'm trying to make a graph that shows the amount of letters received and mailed in each month. I know there are probably numerous ways to do this but I'm looking for the most effiecent and I would like to be able to update the same spreadsheet and have the graph update as I add information.
I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.
When estimated completion date is 1/1/2099 'on time' should be 'no' When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no' When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'
And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.
I'm trying to write code so that it uses an offset value depending on criteria. In the attached book I have three coloumns, firstly the date, secondly some letters, an If statement in the third coloumn and another IF statements in the fourth coloumn. I would like a macro that calculates the number of DAYS ONLY, depending on the fourth coloumn. The way it should operate is it should is detect a 1 in the D coloumn and then calculate the difference in days from the adjacent date in coloumn A and subtract from the first time it appears in coloumn A.
For instance in the worksheet, the first 1 in coloumn D is in 'D5'. The date in 'A5' is 04/01/2005. The first time a '1' appears in coloumn C is C2 and its date in 'A2' is 01/01/2005, so 04/01/2005-01/01/2005 is 3 days. Now heres the twist, the next 1 in the D coloumn is in 'D8' and hence the date in A8 is 07/01/2005, now this time the '1' in coloumn C is in 'C7' and hence the new calculation is 07/01/2005-06/01/2005. So hence the 1 in coloumn C is always changing (ie new offset value).
I have a start date, and an end date for a promotion. However, I am trying to create some unique keys for a VLOOKUP, so I can combine the data I have by day, with the data I have for promotional start/end dates.
So, simple question, if I have a start date and an end date, is there was way (either using excel or vba) to pull out all the dates within that range and display them on a spreadsheet?
For example, I have 21/01/2012 and 28/01/2012 as the start and end dates. I would want to display 21/01/2012, 22/01/2012, 23/01/2012 etc.
I have a macro I am using that uses several date filters. There are filters for the previous month, previous 6 months, previous year, all dates in the future, etc.
Every month I have to edit the macro and change the dates in the macro so it remains accurate. Is there any way I can modify the macro so that the dates are stored in a cell within the workbook and not in the macro?
What I would like is to type the date ranges in a sheet within the workbook and have the macro look to those cells for the date filter instead of typing in exact date ranges within the macro. This way the actual macro code does not need to be modified every month. I would like to only have to change cells within the worksheet and not touch the macro code.
I am currently assigning dates to a list manually by giving 5 dates on Mondays to Fridays. I currenly need to do this for around 500 rows and as you can imagine this takes some time.
Is there a way to have a formula that would assign for instance 5 dates on each weekday, purley by dragging the formula down?
I have Column A with chronological dates. Some of them will be the same. If the dates are the same then the lowest row of those dates would need to add all the corresponding values in column D and print the total to column E
eg. A1 through A3 = 2/12/2009 D1 through D3 would need to be added E3 would display total for =d1:d3
it would then do this for each of the dates that match in Column A
I am using the following formula to calculate due dates based on the rank i assign an issue.
Note: a similar formula is used for the due date of section one, but if it is not complete, i do not want this formula to kick in a determine the due date for section 2. The formula itself is working fine, but is giving a 'due date' of 1/30/1900. I was wondering if there is a way to not display anything if it has no number to calculate from.
I am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).
At the moment I cannot format the axis and minor/major units remain numbers.
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then _ Target.Offset(0, 14).Value = Now
End If End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I have dates in column A (Source Dates) and Column C (Target Dates). All data is formatted as Dates. I want to find which dates in column A have a matching date in column C. When using the find statement within a For Each loop I can not find a date match unless I format the target dates as General. How can I use the Find Statement using dates without formating the target dates as General?
Option Explicit
Dim SourceDate As Range Dim TargetDate As Range
Sub FindTargetDate() Columns("C:C").Select Selection.NumberFormat = "General" For Each SourceDate In Range("A1:A32") 'MsgBox SourceDate Set TargetDate = Sheet1.Range("C1:C7").Find(SourceDate, LookIn:=xlValues)
I am currently using an Intersect statement in a worksheet module to perform two things: 1. Insert a time stamp into row 2 when row 1 has a price inserted 2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user. If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I have done the two obvious checks regarding my missing Status Bar, both in View and Options and still the Status Bar refuses to show itself. I have shut down Excel with the options unticked hoping that on restarting and ticking them the bar will re-emerge but still no joy and also done vice-versa. how to get the Status Bar to reappear?
