count formula combined with dates

andrumu

New Member
Joined
Jun 12, 2008
Messages
17
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 12, 2008
Messages
17
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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 12, 2008
Messages
17

ADVERTISEMENT

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 12, 2008
Messages
17

ADVERTISEMENT

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 12, 2008
Messages
17
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top