Count Unique Months Between Date Ranges
Oct 12, 2009
I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?
View 9 Replies
ADVERTISEMENT
Mar 11, 2014
I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.
I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.
For example, if the HC per month would be:
May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5
in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?
Month
Type
L/H
Prod type
HC
[Code].....
View 9 Replies
View Related
Sep 20, 2013
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
View 2 Replies
View Related
Aug 14, 2014
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx
View 5 Replies
View Related
Jun 11, 2008
I am trying to count the number of instances of dates that occur in a single column. For example, I want to be able to count the total # of dates that occur between Jun and July, then a different count of dates occurring between Aug and Dec (see example below). I have tried this formula: =SUMPRODUCT((E11:E149>=1/8/2008)*(E11:E149<=12/31/2009)) but it comes back with zero.
Column A
---------
1/8/2008
5/8/2008
8/18/2008
9/18/2008
View 2 Replies
View Related
Feb 19, 2010
I have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):
View 6 Replies
View Related
Jan 16, 2013
I want to work out how many cells in a colums are "equal to or more than AND less than or equal to" certain date ranges.
E.G. Column A has random dates from 01/10/2012 to 31/12/2012. I want to know how many of these cells have dates that meet the criteria of >=01/12/2012 and <=31/12/2012.
I have searched on here and found COUNTIF which didn't work. I also tried DCOUNT which I couldn't get to work.
View 2 Replies
View Related
Oct 30, 2013
I have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.
Start
Stop
Month/Year
[Code].....
View 4 Replies
View Related
Oct 23, 2009
My setup is - excel 2003 sp3 / windows xp
On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.
Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.
=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
View 9 Replies
View Related
Apr 27, 2009
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies
View Related
Jan 26, 2008
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
View 9 Replies
View Related
Jul 19, 2009
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Jan 4, 2006
Is there a way to count the number of times an entree is entered in a spreadsheet.
example In row c i have dates entered
11/1
11/5
12/6
12/9
11/3
etc.
Is there a way to count the number of times that the month of dec was entered.
View 14 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 Replies
View Related
Jun 11, 2013
What I am trying to do is take the dates in Column B and count each date as a month, and put those totals into the January through December boxes. I've put in the amounts that should be shown with the data I provided.
View 2 Replies
View Related
Sep 26, 2013
I have a spreadsheet that I am using for capacity planning. We want to automatically figure out which months have 4 weeks and which months have 5 weeks.
The weeks in a month are defined by work week (Mon to Fri), and if the work week has 3 or more days, it is considered a week of that month, if it has two or less days, it is not counted as a week during that month (it will be counted part of the following month).
ie: April 2014 would be a 5 week month, May 2014 would be a 4 week month and June would be a 4 week month.
Apr-14
May-14
Jun-14
Jul-14
Aug-14
Sep-14
Oct-14
Nov-14
Dec-14
Jan-15
Feb-15
Mar-15
[code].....
View 9 Replies
View Related
Nov 28, 2013
I have a column of dates, and wanted to count how many of them are within 3 months of todays date.
I wrote this obvious formula, or so I thought, as it displays zero results, when here should be loads.
=COUNTIF(A:A,>(TODAY()-90)")
View 3 Replies
View Related
May 15, 2009
The below formula is a small piece of a formula but it's where it's causing the error to occur. When I'm trying to count the months within the SUMPRODUCT formula it works fine, but when there is a "TOTAL" it's returning an error.
AB11/04/2009#VALUE!22/04/2009 33/04/2009 44/04/2009 51/05/2009 62/05/2009 73/05/2009 8TOTAL 95/04/2009
Spreadsheet FormulasCellFormulaB1=SUMPRODUCT(--(MONTH(A1:A9)=4))
View 9 Replies
View Related
Apr 15, 2014
Column A has current building, column b has future building. Would like to count the number of changes without adding a separate column with an if statement.
View 3 Replies
View Related
Mar 20, 2014
I have a problem again with one of Date Dif function " =DATEDIF(I5|J5|"m") " in attached file i have this function applied to calculate number of months in between two given dates but it seems this function is not working properly or i am missing some info in this to make it run properly. How to fix this function or give me a proper one which can solve my problem to calculate accurate months between these dates in attached file...
Excel Date problem.xlsx
View 14 Replies
View Related
Mar 27, 2014
Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls
View 14 Replies
View Related
Oct 31, 2012
i have first date in cell a1 and second date in cell b1. I want formula to to how many months are in between these two dates,, for example
first date: 1 jan 12
swcond date: 5 apr 12
result: 4
View 9 Replies
View Related
Jan 22, 2007
Attached is the spreadsheet where I was trying in to accomplish the following.
1. I have two date columns (From date and To date) eg. 1-Jan-07 to 31-Dec-07.
2. I also have other columns having month-yyyy. eg. March-07, Aug-08 etc.
3. I need to compare Mar-07, Aug-08 etc. with From and To date. If monh-yy falls within these dates, I should populate "1" in that cell.
View 3 Replies
View Related
Mar 4, 2013
I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).
What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.
I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.
Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.
VB:
Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]
I have tried to make this dynamic but have been unsuccessful so far.
Spreadsheet with data&code is attached.
VB:
Sub Macro1()
Dim mainrange As Range
Dim rng As Range
[Code].....
View 3 Replies
View Related
Mar 20, 2014
I'm trying to filter 2 date columns to include only data containing dates within 3 months of today's date.
I see there is a data filter option for "next quarter" but not 100% sure if this covers the quarter from today's date?
View 3 Replies
View Related
Aug 8, 2014
I want a formula that counts all days of a month "listed in a column" but excluding sunday.
View 14 Replies
View Related
Jan 25, 2010
I have a start date for a contract in cell AM1. I need AN1 show a date six months from the start date in AM1. I am fine with that part.
However, I would like for AN1 to calculate not only a six-month date but also to auto-update to the next six month date from AM1 once the first six month date is about, let's say, one month past. What I am doing is calculating when a six-month inspection needs to be completed; these inspections are ongoing, so I need them to auto-update.
So let's say a contract was awarded today, 25 JAN 2010. That would be value in AM1.
I want AN1 to produce a date six months from 25 JAN 2010, which would be 25 JUL 2010 (yes, this way of calculating the six month date is fine). Then around, let's say, 25 AUG 2010, I want AN1 to auto-update to produce the next six month date, which would be 25 JAN 2011 (six months from 25 JUL). Obviously the function would need to relate to TODAY() in some way.
View 11 Replies
View Related
Mar 24, 2009
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.
We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.
For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.
In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.
Here is the formula:
=(FVSCHEDULE(1,M79:M90/100)-1)*100
View 2 Replies
View Related
Mar 7, 2008
What's unique about row and column ranges compared to vanilla ranges?
1. This works (it calculates the deviation of each column in a range): ...
View 9 Replies
View Related
