# Formula To Count Dates Between 2 Date Periods If Specific Condition Exists

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
ADVERTISEMENT
Nov 30, 2013

I am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.

My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.

Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013.

Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23

Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B

So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.

View 4 Replies
View Related
Jan 19, 2008

One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

View 4 Replies
View Related
Aug 4, 2008

I have been using a formula to count the number of days between 2 dates and if the date was older than 12 months it would work fine.

The data that i was using has been changed and they have decided to change the format of the dates and dropped the year reference no all i have to work with is dates like 27-07 this represents 27th July i can format this date to work fine unless the date falls before 1 jan this year all i get is lots of numbers and it stuffs up the whole calculation.

The formula i am using to compare the dates is

=DATEDIF(N31,$D$1,"d")

View 9 Replies
View Related
Oct 24, 2013

I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A

For Example:

Sheet 1

Column A

Apples

Oranges

Banana

Fruit Salad

Fruit Smoothie

[code].....

View 6 Replies
View Related
Jan 3, 2013

I'm trying to create a formula that tells me the following:

If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.

View 2 Replies
View Related
May 23, 2008

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.

View 7 Replies
View Related
Jan 17, 2008

I found these 2 examples online on how to count Mondays between 2 dates but neither seems to work on Excel 2003, which I am using. Can anyone help me please?

Number Of Mondays In Period

If you need to return the number of Mondays (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:

A2 contains the beginning date of the interval

B2 contains the ending date of the interval

C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday) ....

View 9 Replies
View Related
Dec 28, 2011

I have a column of over 20,000 rows, showing employee hire dates. For the purposes of a specific calculation, I want to replace all the hire dates that are prior to 1/1/2011 with 1/1/2011. Is there a simple way to do this all at once with a Replace statement in my code without having to look at each record individually?

I know how to replace one specific date with another all at once, but I don't know to do it when I'm looking for more than one specific date.

View 6 Replies
View Related
May 14, 2008

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): ...

View 4 Replies
View Related
Jan 22, 2008

I want to be able to count the number of days in a specific year between two dates.

Suggested formula input: DaysInYear(Date1,Date2,Year)

Examples:

DaysInYear(3/3/2005,3/3/2006,2006) should return 62 (31 in Jan, 28 in Feb and 3 in Mar.)

DaysInYear(3/3/2005,3/3/2007,2006) should return 365

View 4 Replies
View Related
Aug 30, 2007

What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07:

06/30/08 10,000

08/31/14 18,000

05/31/08 11,000

07/31/09 25,000

06/30/10 14,000

12/31/25 80,000

and the amounts need to be distributed in columns titled:

Within 1 Yr (From 7/1/07 to 6/30/08)

After 1 to 5 Yrs (From 7/1/08 to 6/30/13)

After 5 yrs (After 7/1/13)

View 9 Replies
View Related
Feb 26, 2014

My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.

View 2 Replies
View Related
Jul 30, 2009

I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.

So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.

View 3 Replies
View Related
Dec 28, 2013

I have a yearly running log (attached). At the bottom in cell [B88] I would like to develop a formula that gives me the number of times I ran in that specific month. Dates are in Column A and running distances are in Column B. If a distance is zero, I don't want to count it. I have attempted to solve this using the =COUNTIFS formula, but I am not able to structure it properly. Maybe =COUNTIFS is not what I should be using.

View 3 Replies
View Related
Dec 7, 2007

I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd

I desire to look in column "E" and I have 3 different variables. "J", "K", "L".

If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.

for Example: 19630923 j = 19930923 k = 19980923 L = 20010923

View 5 Replies
View Related
Mar 20, 2009

I am trying to find a way to identify the nmber of periods where a particular value is exceeded.

Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.

Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.

The data is formatted as follows: ...

View 7 Replies
View Related
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
View Related
Mar 15, 2009

I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.

View 14 Replies
View Related
Mar 16, 2009

problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB.

Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.

View 7 Replies
View Related
Feb 14, 2009

i want a function that tell me how many duplicates are in the ID for a particular text.

View 9 Replies
View Related
Mar 27, 2008

I've got a 2007 Excel spreadsheet that will count dates within a range using the COUNTIFS function. I am having to send this spreadsheet to users that only have Excel 2003. I need to know how to convert this so it works in 2003.

Since I'm doing this for a fiscal year I have to use a date range. Here are my two formulas I'm trying to convert:

=COUNTIFS(D9:D25,">3/1/08",D9:D25,"<6/30/08")

and

=COUNTIFS(D9:D25,">7/1/08",D9:D25,"<6/30/09")

View 4 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
Aug 27, 2007

Its a training list, and I want to count the number of staff with valid training dates, I want to keep invalid dates as a reminder and I also have text N/A to disregard. Have used an IF function array but there are approx 33 column entries I want to add and using array function limits the amount of formula entries up to column 24.

Would be much easier if I used data validation to kick out the invalid date entries but we want to keep them if possible.

View 9 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
Mar 4, 2008

I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example.

14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178

View 9 Replies
View Related
Apr 9, 2014

I have a range of dates B1:B100 which I want to compare to a certain date A1 and see how many are greater than my stated date A1.

I've tried with countif(B1:B100, "=>A1") which I thought would work, but it gives the error Value!

How do I do this?

View 1 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
Dec 21, 2008

I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months.

I have three letters that occur in column B of sheet 2.

A for active, P for pending, and S for sold.

The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows.

I would like to total the sales for each period and place it on sheet 1.

can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.

View 9 Replies
View Related
Feb 20, 2008

Needed count formula incombination with dates. I need to count the total of names in a colum, but I need to count them for a set period.

View 9 Replies
View Related