T
tofimoon4
My dear, please tell me ( with example) how to calculate values between
two dates .
two dates .
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Sandy Mann
Speaking for myself, I am not sure what it is you are asking. Why don't YOU
give US an example for what you want.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
give US an example for what you want.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
T
tofimoon4
Sandy said:Speaking for myself, I am not sure what it is you are asking. Why don'
YOU
give US an example for what you want.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
my dear, i mean if i have invoices with several dates like below :
1/5/2007 700
1/12/2007 1250
1/26/2007 750
2/6/2007 2000
2/15/2007 1850
2/25/2007 3200
3/1/2007 7500
and i need to calculate these values every 15 days or monthly
S
Sandy Mann
Monthly Totals:
January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))
February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))
March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))
You don't say where the 15 days start so assuming that they start on 1/1/07:
1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)
Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)
Etc.
That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))
February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))
March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))
You don't say where the 15 days start so assuming that they start on 1/1/07:
1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)
Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)
Etc.
That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
S
Sandy Mann
I forgot to change the dates in the 15 day formulas from my British style
into American style.
It would be better to place the date in cells - say 1/1/07 in C1, 1/15/07 in
C2, 1/30/07 in C3 etc. then the formulas would be:
1st 15 days:
=SUMPRODUCT((A1:A7>=C1)*(A1:A7<=C2)*B1:B7)
2nd 15 days:
=SUMPRODUCT((A1:A7>C2)*(A1:A7<=C3)*B1:B7)
No need for the confusing double unary.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
into American style.
It would be better to place the date in cells - say 1/1/07 in C1, 1/15/07 in
C2, 1/30/07 in C3 etc. then the formulas would be:
1st 15 days:
=SUMPRODUCT((A1:A7>=C1)*(A1:A7<=C2)*B1:B7)
2nd 15 days:
=SUMPRODUCT((A1:A7>C2)*(A1:A7<=C3)*B1:B7)
No need for the confusing double unary.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
Sandy Mann said:Monthly Totals:
January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))
February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))
March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))
You don't say where the 15 days start so assuming that they start on
1/1/07:
1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)
Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)
Etc.
That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk