Count Unique Dates In A Column That Are Between Two Dates?
Dec 18, 2012
I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:
I've come across a formula that I can't seem to get my mind fully around. I have a time-tracking sheet for employees that includes, among other fields, DATE and MILEAGE.
In my particular spreadsheet, DATE is from A2:A31 and MILEAGE is from G2:G31. The date column is set to the correct data type (date) and mileage is a general field and only has integers entered into it.
What I would like to do is count the number of unique dates on which mileage has been entered (field is not empty). If the total mileage for a day is less than 20, then do not count it. The reason for this is because an employee is entitled to be compensated for the total mileage of all service calls of a particular day minus 20 kilometers. If an employee, for whatever reason, enters say, 5km for the day, they should not be deducted 15km as would happen if the date was counted.
For example, if Jack traveled a total of 500km spread over 5 days, he is entitled to be compensated for 500 - (20 * 5) = 400km. The trick here is that if Joe has time billed on 6 unique days but only entered mileage on 5 of them, the formula still needs to return 5.
This formula sums up the total mileage traveled and then subtracts 20 * number of unique days. The problems with it is that it still counts days on which no mileage was claimed (corresponding mileage field is empty). In addition, if the total accumulated mileage for a day is less than 20 then it is also still counted.
I want to count the unique values between 2 dates. He only needs to count the values that match 2 criteria. I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.
In my Data sheet: Colomn X: Customer name Colomn D: Status Colomn AH: Date of order (Day/Month/Year)
I want to count the unique values in Colomn O: Ordernumbers
I have a group of employees who work different divisions and work is not done everyday. I want to count the unique values of a division (only once) per day that the division shows up. Within a week, workers in a division might work 3 days and not other days. I've tried a few array formulas but they count unique within a range and I need the unique value to be based on the selected cell.
Please see attached : Unique Values In Between Dates.xlsx
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
I have a range of data in a spreadsheet that has entry dates. To one side I have a cell that =today() What I want to do is where the entry date is in my range of data is insert a column that will tell me "yes" if the entry date =today()-1
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them. note some are on the left and some on the right.
I have a specific issue that I have not been able find in here or on the internet. I need to count entries in one column based on a date range using corresponding dates in another column.I found countif, countifs, dcount, etc but have not been able to get any code to work. I don't know if my excel spreadsheet attached correctly but I can explain it since it's a simple one. Column A is filled with report dates that generally increase from 10/1/2012 t0 9/30/2013. Column B is filled with the number of "AFIS searches." If an AFIS search was not conducted for a specific report then the cell is blank. I need to count the number of AFIS searches between the dates of 1/1/2013 and 3/31/2013.
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)). So now I just need it broken down by training type per month. How many x's in each column for February as an example.
Each product is represented by a serial number (column A). The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
I have searched through a lot of other posts, but I can't seem to make the recommendations work for my given scenario. I have a sheet that compiles information for different dates (based on a query that is run by the user), and in column H I have a start time that is listed in this format "m/d/yyyy h:mm."
What I need to do is find all of the unique dates "m/d/yyyy" from this given range, and return them in column O.
For example, I will have the following information:
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
I have searched for a 'simple' solution to this but I only ever seem to find complex and indepth examples.
I summarise web hit information then chart it so I can look for trendlines. I get good info from the stat company so I can see where folk are coming from, search engine, weblink etc etc.
Once I have summarized it I have date running along the column headers then it is broken down through the rows so I have
Total Hits Total Paid Advertising Total Search Engine
And so on for my rows
As the days go on the data gets harder and harder to read so I was hoping I could use some kind of OFFSET function to select my data. So if my dates run from 2/11/07 to today I would like to perhaps say to Excel that I might want to see 20/11/07 to 20/12/07 in my chart.
I have plenty space to put the start and end date and I have a basic block of data.
Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.
For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.
I then have another table and need to total the pallet quantity by month. Does anyone know a formula where i can have a TOTAL pallet figure by month, therefore showing October as having 53 pallets?
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit 413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012 18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
