Count Simultaneous Rows With Same Cell Value And With Time / Date Condition
May 9, 2014
I have rows of data in excel that update automatically and i want to check in column B how many times there are simultaneous occurrences of a cell value and then if those occurences have been there for a time specified time duration using column C which has a time and date stamp. The time and date stamp is when the price is saved down.
A Simplified Example:
Item
Action
Date & Time
Red
Buy
09/05/2014 09:00
[Code] .....
What I would like the formula to is to count the occurrences of cell B that have been avilable for longer than say 30 minutes.
Formula that worked out the occurrences of cell B with this formula
=SUM(IF(FREQUENCY(IF($B$2:$B$7="Buy",ROW(B2:B7)-ROW(B2)+1),IF($B$2:$B$7<>"Buy",ROW(B2:B7)-ROW(B2)+1)),1))
So if i wanted to count the simultaneous occurrences of "Buy" then the formula would result 1 and the same for sell if i changed the formula would be 2.
Now i want to try and get a way of using the time those occurrences were available for by using Column C
The simultaneous "Buy" was available from 09/05/2014 09:00 to 09/05/2014 11:01 which is longer than 30 minutes so i want to count that as a occurrence.
Then both the "Sell" were only available for 14 minutes the first time and then only for 20 minutes the second time so none of those would count.
View 1 Replies
ADVERTISEMENT
Feb 22, 2008
Column 1: Date Call is received by helpdesk
Column 2: Time Call is received by helpdesk
Column 3: Drop-down list indicating Priority of call (High,Medium,Low)
In Column 4 I want to enter a formula that adds working hours only to the date and time entered in Columns 1 & 2. The time added will vary depending on what is entered in Column 3 (e.g.: if priority is High add 1 hour, Medium add 2 hours, Low add 3 hours). The working hours i need to adhere to are 09:00-17:00, Monday-Friday.
View 6 Replies
View Related
Apr 6, 2012
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
View 3 Replies
View Related
Oct 20, 2009
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
View 5 Replies
View Related
Dec 17, 2011
I've done this before but can't remember how I did it:
1BCDEFGH21234533red92701131096601005096604green20070582305250044940472805
blue0355203912033930389706bpink51059230632205352061280789-In column H,
I want to sum only rows that are less than or equal to cell H2.....
View 2 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
Apr 26, 2007
I have data in 3 columns: Name, Inclusion Date, Exclusion Date. In some instances, the inclusion date and exclusion date will be zeroed out - I do not wish to count those. What I would like to count is the rows that have an inclusion date, but not an exclusion date. Ideally, I would like to have a code so that when I run a macro a msgbox appears that indicates how many members there are.
View 12 Replies
View Related
Nov 15, 2006
I have a spreadsheet which looks like this: http://img46.imagevenue.com/img.php?..._122_521lo.JPG
I'm trying to figure out how to set up a macro which can generate a correlation table for each row. For instance, I would like for the macro to cycle through the row labeled 'List1' and record each cell value (B1, C1, D1, etc). Then, I would like to compare those values to the row labeled 'List2' to determine how closely they correlate. If List1's columns were identical to List2's columns, (order does not matter), I would want those two to have a correlation of 1 or 100%. If none of those two lists' columns match, I would want a correlation of 0 or 0%. In the end, I would like to only keep the List rows which are most uncorrelated with each other.
View 4 Replies
View Related
May 28, 2014
I would like to check if there's a way to count the number of unique value with a given condition
Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector
Attached is the files, and my desired result is highlighted in RED.
View 3 Replies
View Related
Feb 6, 2013
figuring out a formula to count the number of occurrences within a date range (the month of October) however, the date column is formatted to mm/d/yy 00:11:22 PM/AM.
I tried using =COUNTIFS(B4:B96,G3:G9611/1/2008) but got an error.
This is what I'm working with (there are other occurrences for Nov and Dec in the spreadsheet - this image only shows Oct)
Site Statistics
October-December 2008
Visitor
No.
IP
Address
Operating
System
Browser
Site
[code]....
View 6 Replies
View Related
Jul 19, 2014
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.
View 7 Replies
View Related
Dec 17, 2013
I have a spreadsheet with rows and rows of data by date. The far left column houses the date and then each row is another occurrence for that date. Basically I need to count how many rows have the same date. Here is what the basic sheet looks like:
6/15/13
6/15/13
6/15/13
6/15/13
6/15/13
6/16/13
6/16/13
6/17/13
6/17/13
6/17/13
So the formula I am looking for would return, 6/15/13 =5, 6/16/13 = 2, 6/17/13 =3. Or something along those lines. I have a ton of data and using pivot table info to filter by each day will take FOREVER.
View 8 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Sep 6, 2006
I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971
View 6 Replies
View Related
Apr 12, 2014
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
View 9 Replies
View Related
Dec 28, 2006
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?
View 6 Replies
View Related
Mar 9, 2008
I have a problem regarding sorting data having date and also time within a single cell.
Example data (I have written it as code to preserve formatting)
A B C D E F
12/5/2008 02:072/5/2008 06:0128804833363
22/5/2008 18:012/5/2008 18:0599271297
Column B is start date and time whereas Column C is end date and time.
My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.
View 9 Replies
View Related
Aug 21, 2013
I have a spreadsheet where an engineer is expected to record sample temperatures of water outlets, along with the time he took the sample. Each outlet has a row on the spreadsheet with a column for the Temperature and column for the time. I would like to automatically input the current time(or time and date) on each line as the temperature is entered.
View 4 Replies
View Related
Apr 18, 2014
I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
View 9 Replies
View Related
Apr 27, 2008
Two Sheets:
1) Top Sheet
2) IRA
- Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A:
____A__________|_B______________________
4 | 09/22/2005 | $ Total as of date in A4
5 | 10/06/2005 | $Total as of date in A5
6 | 10/20/2005 | $Total as of date in A6
7 | 11/03/2005 | $Total as of date in A7
8 | 11/17/2005 | $Total as of date in A8
9 | 12/01/2005 | $Total as of date in A9
10| 12/15/2005 | $Total as of date in A10
- Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range:
____A__________|_B______________________
6 | 09/22/2005 | $100
7 | _ _ _ _ _ _| $200.................................
View 4 Replies
View Related
Mar 26, 2013
In a particular sheet the data (with formulae) are present from rows E5:Q5000. I require a macro which automatically does the following:
- When user enters “submitted” in the row Column N, entire rows above that cell should be selected, and copy-pasted as values to remove all formulas. So if user enters “submitted”, at N31, then data set E5:Q31 should be selected and copy pasted special so as to retain only values.
-Finally the copy pasted data should be sorted as per the status of Column N.
View 4 Replies
View Related
Apr 5, 2014
I am working on a schedule worksheet, the days are the columns and the staff member names go down the rows. I want to create a new cell value based on a condition on each row. I have been successful working with one row but how to efficiently do the same for each row.? I'm working with Excel 2010.
View 9 Replies
View Related
Mar 2, 2014
I need to subtract the time alone as per below example:
Cell A : 2/4/2014 8:58:13 AM
I need in Cell B as : 2/4/2014 8:46:13 AM (Minus 15 minutes)
How to get this?
View 2 Replies
View Related
Apr 22, 2009
I have data exported from a defect management tool (that reports all the defects that have been recorded in the defect management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc. The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low priority defects were created on each date - and I am using a pivot table to do this.
However, because the date column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date - with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2 against Medium.
I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data (but doesn't in the pivot table, because the time stamp is still there, just not visible). I have tried copy and paste special [Values] (on the custom formatted date column) -but the time stamp is still there. I have tried formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23 becomes 39842.5166203704.
I have tried inserting a new column and using the =Left function for the first 10 characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie 39842.5166. Basically, without going into every date cell (and I have thousands) and manually deleting all the times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?
View 3 Replies
View Related
Dec 18, 2009
I have set up a workbook containing 15 sheets. 12 of them are named Jan to Dec. I KNOW how to protect each one, but is it possible to protect all twelve in one go?
View 9 Replies
View Related
Jan 18, 2007
It is my understanding that two or more people can simultaneously work in and save data in a given workbook.
Is this true?
If so, how does one set up the collaborative mode? Currently we are getting "the first person in has control of the file, all others are 'read only' until the file is released".
View 3 Replies
View Related
May 18, 2009
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
View 3 Replies
View Related
Apr 2, 2008
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?
View 4 Replies
View Related
Jul 2, 2008
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.
Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16
View 4 Replies
View Related
May 23, 2014
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
View 5 Replies
View Related
