I have a Column (G) of dates that is the sum of Column (B) and 6 months. I have conditionally formatted Column (G) to turn RED when overdue Yellow when close and GREEN if more than a month out. At the end of Column (G) I would like for it to add all the "RED" blocks and total them.
View 12 RepliesI have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2
I have a spreadsheet where I am tracking tasks. I would like to conditionally format the "Completed" cell red whenever the Due Date has passed to easily identify which tasks are outstangin. And I would like that to happen no matther what date someone opens the spreadsheet. (I think I need to use TODAY() here.) Then once the user enters the actual completed date, the cell would go back to white with black font. Below is a sample:
Due Date Completed
5/8/2006 5/8/2006
5/8/2006 5/8/2006
5/12/2006
8/30/2006
This seems like it would be easy to figure out, but I've spent days on it and am not thinking clearly any more.
I am an office worker in a (very) small business, and so we use excel as a simple database, which covers our needs easily. In particular, we use a sheet as a register of fees. The question I have involves the creation of a macro to find over-due fees.
The format of the sheet is:
A.Invoice date | B.Reference code | C.Client name | D.$$ cost | E.Due date (always 14 days from invoice date) | F.Payment received date | G.$$ Received
At current, when a fee is overdue, we manually check the list for unpaid fees past the due date and highlight them (generally yellow) and then copy and paste these to a seperate sheet (same book). I believe this could easily be accomplished with a macro, but I am not at all fluent in the coding.
What I believe the macro would require to do is sort through the list (first invoice in row 4), Check ifblank for column B (to make sure a fee exists), then check current date against due date. If 'overdue', check that G=D (received = cost). If not, highlight that row and copy to row 1 of new sheet. then return to fee sheet, move down a row, rinse and repeat untill out of fee's.
If column B is blank, then it might be the 2 row gap left between each month, so it would need to check ifblank for 3 consecutive rows before ending the macro.
If due date is blank (which happens sometimes because we enter drafts into the sheet, and just leave the dates blank until we send them out, but still has a ref code) then it should count as 'not overdue' and move on.
I think it'd be better as an on-click macro rather then an automatic macro, since it only needs to be done at certain times, not every time we fiddle with the sheet a bit. Being on-click, it could even use the same sheet to copy all the overdue's to (rather then create a new sheet every time), as long as it 'select all - delete's it before copying the new things.
Thanks for any help. I signed up just to get this help, so I deeply appologize if I have broken some sort of policy or rule.
I'm trying to build up a spreadsheet with the info below to follow up the due and overdue invoices. My point is to highlight the whole row for an unpaid invoice at the due date.
Here's the basic structure of the document:
A colum: invoice ref
B : name of customer
C : Invoice date
D : Due date
E and F: Amount in EUR and USD
G : Paid (Yes/No)
1. I'd like to highlight the whole row in red when the invoice is due/overdue AND unpaid (G = No). That means 2 conditions.
2. When invoices are paid (when G = Yes), the row should be green.
3. Highlight the whole row in orange when the invoice is unpaid and due in 10 days.
I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx
View 2 Replies View RelatedI've used conditional formatting to highlight dates which are due this week and next week, but now I need to highlight which dates are overdue.
Each row is a task/job.
I have a 'Due Date' column.
I also have a 'Date Completed' column.
How do I highlight the overdue dates based on their due date (not today's date)?
And once I have entered a date in the 'Date Completed' column, I need the overdue highlight to be removed.
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies View RelatedIs there a function in Excel that will count cells that are different from one another? I.e. it would not count the same value twice. For example, in column A:
a
a
b
a
b
b
c
a
c
The result would be 3. However, if I replaced the first "a" with a "d" I would get 4 as a result. Basically I want to know how many different arguments there are in a list. I could not find a function that does this in the Excel list of functions.
I am now attempting to do the same type tally except I want a tally for "in conference" Wins/Losses.
I tried the same "SUMPRODUCT" formula as before except this time I held the control key down so as to use only the cells desired. I'm getting "VALUE" for my answer though.
The row I have added in for in conference tally is row 3.
All blue colored cells are for the ACC conference and the green are for the SEC.
Is there a way to get the win/loss figures for selected cells / rows only?
I am trying to create a formula to count the # of names up to a certain word. So Cells A1-F1. Are a list of names, the Last name in the list starts with VACANT. How do I create a formula to count the # of names in the list upto the work VACANT? So if VACANT moves to C1 it would decrease the # of names.
View 9 Replies View RelatedI have written some VBA code to to test if there are 8 names within a range (EF5:FH5, these are 8 merged cells, (4x4 cells)), if there is 8 names within the cells, ie not any empty cells, then the coding will add the new staff name to "trigger_box_1b", and if not then "trigger_box_1a". These trigger boxes are then used to populate further cells.
Private Sub Trigger_Box_1_Change()
Windows("Admin Skills Matrix.xls").Activate 'puts focus on correct spreadsheet
Sheets("Skills Matrix").Select 'puts focus on correct page
Select Case WorksheetFunction. CountIf(Range("EF5:FH5"), 0).double
Case 0
Trigger_Box_1b.Text = New_Staff_Name
Case Else
Trigger_Box_1a.Text = New_Staff_Name
End Select
End Sub
the problems I have be having is that VBA is not recognising the Countif function ("Compile Error: Invalid qualifier", then highlights the Countif). I have tried using a CountA function with the same responce. I have tested the rest of the coding and know that it's working fine (don't think anyone would like to see 75+ pages of VBA coding)
I know this question may have been posted before, but I can not seem to get this to work with my formula. I have a formula to count the values of a column so that I can convert it to a graph. I am giving a value of 1 to cells that contain numbers between 0 and 9.5 and giving a value of 0 to cells that contain numbers greater than 9.5. It works fine except that I have some blank cells that I do not want to get rid of and it is counting those as zeros and giving them a value of one. I want to ignore the blank cells or give them a value of zero.
=IF(AND(F2>=0,F2
My data is as follows:
Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Number of Starts 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Rolling Number of Cases 10 20 30 40 50 60 70 80 90 100 110 120 120 120 120 120 120
In my dataset above the cases finish after 12 months (hence why there is a maximum number of cases).
What I am seeking is a method to count along a certain number of cells, then sum the contents of those. For example, I would like to count the contents of up to 12 cells, then sum those 12 cells.
Where it gets difficult is as follows:
- In month 6, I am just counting the contents of 6 cells (months 1-6)
- In month 12, I am counting the contents of 12 cells (months 1 -12)
- In month 18, I am counting the contents of 12 cells (month 7 - 18)
way to count cells in a spreadsheet (not values in the cells but just the cells themselves). So I have a whole bunch of cells, some are red, some are blue, some are green based on the info i have to keep track of.
so can i write some COUNTIF for red cells green cells, etc
I have attached a spreadsheet with some tele numbers. What i need to do is to find out which column only has 1 telephone number, eg. only a cellphone number, etc. I have aboout 50 000 records that i need to check if only 1 telephone number is available. Can i use the countblank function?
View 2 Replies View RelatedA cell on sheet1 needs to count the number of cells on sheet2 (D3:D31) that has a complete (top, bottom, left and right) border. This is my best shot at it.
[Code] .....
Sheet1 cell I input =CountBrds('sheet2'!D3:D31)
One issue, on sheet2 for example cell D4 is merged with E4 and F4 as is every even cell in the D column down to row 30. Not sure if that throws a wrench in things or not.
I have a large number of customers listed in an excel sheet that may recieve a visit from my organisation. The reason for the visit may vary and sometimes a customer may recieve more than one visit. Each Row (or record) maps to a customer. Each column has a visit type which I insert a date in to say when the customer has been visited.
I am looking for a function that will return if a customer has been visited or not. As dates can be summed like numbers I am currently saying in the "Visits Recieved" column =if(sum of dates (visit type colunm) >0, 1,0) Then I simply sum the column to get my answer of how many csutomers have been visited.
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula?
I am trying to write a formula for the following can anyone help please?
I have column D with blank cells and some with purchase order numbers in them. I want to count the blank cells in column D but only if the blank is 3days older than the date recorded in column A. Once I have done this I want to count ther same on every work sheet and I have 24 off them and get one answer.
This will tell us all the outstanding purchase order older than 3days
Cells in the range of BG8:BP8 either have a "W" or an "L" in them. I want to have cell BO9 display the total number of Ws and cell BP9 to display the total number of Ls.
View 3 Replies View RelatedI'm struggling to work out a formula to do this even though it sounds simple.
I want to count cells in a particular row or column that contain any data, ideally without having to specify a range, so I just want to know that in column C contains x or row 5 contains y amount of data. So it would look at the entire rown or column and work out how many cells contain something and shows how many.
Is there a way to count shaded cells only , I am stranded in a case where I am waiting reply meanwhile I need to self sort myself with another indirect trick
View 4 Replies View RelatedIs there a formula I can use to add the amount of cells that contain just dates ?
View 6 Replies View RelatedI have this formula that I want to simplify:
=COUNTIF(Table1[t-5],"=NF")+COUNTIF(Table1[t-5],"=NO")+COUNTIF(Table1[t-5],"=NA")
I want the formulat to capture all situations in which either NF, NO and NA are in the range Table1[t-5]
I have a sheet with a bunch of data including dates. I'd like to report out the number of cells that are in a certain month (say January). If I create another column and use =MONTH(A12), I can use COUNTIF(A1:A100,1) to perform this task. This somewhat clunky solution isn't very practicle, becuase I have about 30 column's I'd like to analyze by the date in the column. Is there a more elegant solution where I can incorporate the MONTH(A12) into the COUNTIF equation?
View 9 Replies View Relatedin the Score column I want a 3 for the SnAkEs team and 7 for the dj2 team. How do I total up the bold cells?
View 9 Replies View RelatedI would like to count the number of cells that contain a word. My table looks as follows:
BDL1,500BDL3,135BDL2,1004.31,400254,6001.41144
In Column 1 (left column) I have 3 cells that contain the alphanumeric value "BDL". This would yield a non-numeric count of "3". Column 2 would yield a count of "0". I hope I'm being clear. Anyone hav an idea?
I am doing a simple count and it returns a value of zero.
I am wanting column D that equals "Macro" to return the number of rows that column D has Macro in it? I used = Count and I used = sumproduct and =count returned me a zero value and there are several hundred rows that meet that criteria and =sumproduct returned a zero value as well????? What am I doing wrong? Is it so simple that I'm missing something?
=SUMPRODUCT($D$3:$D$1400="Macro")