count formula combined with dates

[andrumu]

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

 

[DonkeyOte]

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]

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]

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]

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]

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]

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]

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]

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?