I'm working on an excel file where I can see if customers pays too late.
I've got this for the moment:
Date order
Expiration date
Days over
Days too late
Paid?
15/06
15/07
0
-2
NO
[Code] ........
My question is about "Days too late". When Paid changes to "YES", the days too late stays the same number it was before. When it's "NO" it keeps counting.
I am preparing a very large spreadsheet of text. Once I reach a certain point (a few paragraphs?), the program stops wrapping the text. All of my text is visible in the box at the top of the spreadsheet when I click on the cell. I double checked to make sure it's set to wrap, which it is. I tried merging two cells, no change.
View 9 Replies View RelatedI am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
This should be simple, but I am struggling with finding a way to use the search or find function to identify text characters. This is my situation, I have for example a cell that contains FW023 or D1234. I need to be able to count the number of characters that are text.
i would think I would be able to do it with the search or find function, but can't figure out how to get it to just count the number of text characters with in it.
having the formula exclude counting text in cells.
=IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44")=Tracking!$A3)*(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!D4:J44")>8)),"")
I would like it to only count numeric values over 8.0. Currently if I have text in a cell such as "PTO" it returns as a added value.
I'm putting together a calendar that will have show when people are off on holiday, training, out of the office or ill. But as it will identify people by name I have changed to cell style to reflect just what activity they are doing. As such I want to be able to count over a year just who has done what.
Is there a piece of code that can count based on the text inside a cell and it's style e.g. "20% - Accent1"
I have a column that can have a single name or multiple names typed in each cell. I would like to use a vlookup table to match against the cells values. Exact matches are no problem when it is a single name, but I need a formula that matches up the name, but does not need to match the entire cell text (name1, name2, name3,...) and can count the number of cells that contained this text with in a range. In the example above, I have three names.
If those three names are listed in the vlookup table, I want to count each one so that I can sum up that company 1 appeared x number of times with in the column and is x % of all company names, company 2 appared x number of times and is x% of all companies, and so on. My formula to match exact text values looks like this: =IF(ISERROR(VLOOKUP(D4,$H$7:$J$48,3,0)),0,VLOOKUP(D4,$H$7:$J$48,3,0)) This works fine if the cell value is simply company 1, etc.
Greetings, I have a particular column containing data similar to:
X24A
X15B
G15A
X23S
I have sorted the data to have all the X marks together, and now I want to run a counter so i can select the data and delete. Following is a sample of the code that is NOT working:
'Delete X Piece Mark Rows
Range("L2").Select
counter = 0
ActiveCell.Select
Do While ActiveCell = "X" & "*" ' This is the line that crapping out
If ActiveCell = "X" & "*" Then counter = counter + 1
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select
Range("A2", "L" & counter + 1).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
I am trying to create a loop which stops once a cell is empty.
Assuming I have declared proper DIM, my code is
Row=1
Do
Row = Row + 1
SheetName = Cells(Row, "A")
Sheets.Add.Name = SheetName
Loop Until IsEmpty(SheetName.Offset(1, 0))
how can i count the positive days to a inserted date value or the past days (negative value) to the system date (today).
View 5 Replies View RelatedI am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.
View 2 Replies View RelatedHow can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies View RelatedI want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
I have three cells in A2:C2 which require user to input some data.
What I want to achieve is to combine the data from A2:C2 in D2.
C2 is a field which user will input the date. He might key in 21/08/06 or
21/08/06, 30/08/06
I have tried using below formulas in D2 but without success.
=A2&" " &B2&" "&(C2)
=A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates.
I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.
Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.
ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.
I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.
I have a Cell and the Formula for my Cell is the Following:
Code:
="Week Ending " & 'A01'!D248
The A01 Worksheet displays the value as 4/21/2012 . However, the first worksheet displays the value as
Code:
Week Ending 41020
rather than
Code:
Week Ending 4/21/2012
What can I do to display the cell the way that I want to? I've already tried formatting the cell directly as Date to no avail.
I have a column with dates in it. Example "04/04/2007" don't know if format will matter that is why I am showning an example.
I need a formula that looks at that column and tells me how many days were entered for a month. I thought a pivot table would work well but the same date could be entered many times and that would skew the number of days counted. The final result is a table that had each month with the number of days entered. Keep in mind that I only want each day counted once even though it may have been entered 5 times.
I've got a database with a date header across the columns C1 to W1 [C1 value = '02/04/07 and W1 value = '30/04/07]
Each row represents a different person
For each person there can be one of skills: Maths, English, French, Science, Geography on each day. Most people keep the same skill for the whole month but some change multiple times within the month.
What I'm after is a formula which will return the total number of people who have had each skill for at least one day within a time specificed time period. The startdate criteria is in A1 and enddate criteria is in B1
E.g How many different people had Maths on at least one day for the period 16th April to the 20th April.
At the moment I've been trying to work along the lines:
=sumproduct((C2:W200>=datevalue(A1))*(c2:w200
how many dates appear in a column. I have a spreadsheet wherein when a name is encoded in column A, the date is automatically logged in column B. Now, i need to count how many of those dates occurred in column B.
For the month of May - how many 1 May 2007, 2 May 2007...an so on.
After that - i need to match those dates to the name of the encoder and a another encoded status. It's like - 1 May 2007+john+approved = 1 occurence. I need to count them separately and combined.
I need to change the date format in a cell which also contains text.
The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")
This gives me the following:-
Report period:
01 October 2012 - 30 September 2013
What I would like to have is the following:-
Report period:
1st October 2012 - 30th september 2013
It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?
I am trying to concatinate a cell to show both text and date (in format mmmm dd yyyy) using a vlookup from another worksheet.
Below is the coding I have in the cell:
="High Importance Request - effective " & VLOOKUP(1,'Fuel Surcharge Key Dates'!$A$6:$E$65536,2,FALSE)
The result I get in the cell from the formula is: I still get it to come out to 39327 instead of June 4 2007
The vlookup in the formula above retrieves a date that has a cell custom formatted to mmmm d, yyyy ... as this cell is not a date that is entered by a user but is further retrieved by another vlookup of dates. Even if I place the VLOOKUP in cell A3, reformat that cell to be a date and change the coding in the formula to:
="High Importance Request - effective " & A3. I still get it to come out to 39327 instead of June 4 2007
I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating.
The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be
manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data.
I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?
I am trying to work on a spreadsheet (I am very new to all this). I have colum I with dates in and Colum K with dates in... When a product comes into my shop I enter the date it arrived in Colum I and when it leaves I enter the date in Colum K.
What I would like to try to work out is how do I ask Excel to tell me How many blank cells there are in Colum K if I enter a date range for Colum I
I will try to give an example.
Colum K may have 200 entries for the week 6/5/13 to 10/5/13. I need to know how many cells are blank in Colum K if the date range on Colum I is between 6/5/13 to 10/5/13
The end result should basically tell me how much stock I have left in my shop for that week.
I have found something that sort of works by using this =SUMPRODUCT((I1:I200<=TODAY())*(K1:K200="")) but ideally I need to be able to change the word TODAY into a date range like 6/5/13 to 10/5/13 and I cant seem to do that???
I have a spreadsheet that I use to log incidents. In column "A" I have the date in month/day/year format (8/14/2014). I am attempting to automate the graphing process so I have a graph that will tell me how many incidents I had in the month of August. I have attempted the following formulas (and other similar formates with minor changes)...
=COUNTIF('CY14'!A2:A500,"8,2014") and changed around the date format
=COUNTIF('CY14'!A2:A500,"&DATE(8,2014))
=COUNTIF('CY14'!A2:A500,"August")
All of these come up with "0" while I have 17 listed incidents for August. Am I doing something wrong, or is this not possible.
I'm looking for a way to count entries of numbers per day of the week within a date range. Example
Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40
I don't know if that possible in excel or its to much
I have a column of cells with data like the following
07-Dec-09
08-Dec-09
09-Dec-09
10-Dec-09
11-Dec-09
Blank
09-Dec-09
I need a way in VB to say tell me the amount of dates that are less than todays (09-Dec-09) date. Using the above list the answer should be 2. Every formula I try returns the value 0.
I need to do is, on sheet 2 is to have a formula which will count all cells in column A containing dates from 1/1/2009 to 31/1/2009, Another column will count all dates from 1/2/2009 to 28/2/2009 and so on. I have tried the countif format but this does not seem to work.
View 4 Replies View Related
