# count formula combined with dates

#### andrumu

##### New Member
hey i have following problem, i have got a sheet with details of payments. each payment has a date, amount and payment number. heres my request: i want to be able to count the number of payments in a period of three months. additionally i wanna combine it with todays date ( -now() ). but i dont wanna use it from todays date, but for the last quartel which means today we have 25th of june, therefor i would like to count the first 3 months of the year. coming july i would be counting april till end of june. i hope my question is clear enough and i hope somebody could help me with this.

regards,

Andre

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### DonkeyOte

##### MrExcel MVP
given your fiscal year is calendar the following would return current quarter based on today's date:

=TRUNC(DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")/3)

assuming above was in cell A1, the following would give you beginning of quarter date

=DATE(YEAR(TODAY()),1+((\$A\$1-1)*3),1)

below would give you end of quarter

=DATE(YEAR(TODAY()),4+((\$A\$1-1)*3),1)-1

You could then do a COUNTIF where date >= date 1 less COUNTIF where date > date2 or look to use a SUMPRODUCT formula if you feel comfortable with that.

#### andrumu

##### New Member
thx mate, worked perfectly, but i got one more issue on this one though. unfortunately i gotta not only count by date but also per client number. i think an easy countif wont work in this case.

client date number of payment

AA .... .....
AA ... ....
BB .... ....
CC ... ...
CC .... .....

Count payments per client per quarter...

regards,

Andre

#### DonkeyOte

##### MrExcel MVP
can you provide some specifics in terms of the following ?

in terms of the result formulae:
where will you have the client listed ?
where will you have the quarter/dates listed?

in terms of the souce data:
what is the range of the data (eg A1:S10000)
in which column of the range does client appear ?
in which column of the range does date appear ?

with this info we should be able to provide a solution -- invariably this will be a SUMPRODUCT/array formula.

#### andrumu

##### New Member

ok heres my information;

Clientno -> column G
Date -> column I
Debtorno -> column B

the rest is yet to do. but just mention where u have put it when u provide me the formula. i will adjust if necessary later.

thank you

#### DonkeyOte

##### MrExcel MVP
OK let's assume the following:

-- your source data (columns B,G,I) are on a sheet called "DATA" rows 1:100
-- your results are to go on sheet RESULT
-- in Cell RESULT!B1 you have the following formula:
=TRUNC(DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")/3)

-- in cell RESULT!B2 you have the following formula:
=DATE(YEAR(TODAY()),1+((\$B\$1-1)*3),1)

-- in cell RESULT!B3 you have the following formula:
=DATE(YEAR(TODAY()),4+((\$B\$1-1)*3),0

-- in Cell RESULT!A6 (down) you have your client codes
-- in Cell RESULT!B6 (down) you want to return count of instances for that client in given quarter:

=SUMPRODUCT(--(DATA!\$G\$1:\$G\$100=\$A6),--(DATA!\$I\$1:\$I\$100>=\$B\$2),--(DATA!\$I\$1:\$I\$100<=\$B\$3))

#### andrumu

##### New Member

hey,

its a little wierd, the date functions do work perfect, but i cant get the sumproduct formula to run....can u check it again pls

#### DonkeyOte

##### MrExcel MVP
using the example layout I gave the formula will work correctly.

firstly can you check your dates on DATA sheet are in fact stored as dates -- somewhere (doesn't matter where) type

=ISTEXT(DATA!I2)

- is the answer true or false ?

#### andrumu

##### New Member
hey,

i found the problem now. ur formula works fine, it is a matter of date format now...the problem right now is that im transforming a number in a date 1080503 is for example 3th may 2008. if im using date(mid....im just getting 1908 as a date not 2008. do u know how to change that?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,038
Messages
5,856,973
Members
431,843
Latest member
Malahhai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back