Home |
Search |
Today's Posts |
#1
|
|||
|
|||
From x date To x date you have earned x$ (dates are not the same)
COLUMN "A" HAS DATES IN IS AND COLUMN "E" HAS POINTS FOR THAT DATE.
A E 6/11/05 80 6/05/05 100 6/11/05 95 6/05/05 73 6/11/05 230 "I WANT TO PUT ANYWHERE ON THE SPREADSHEET (FROM _____ TO _______ YOU X POINTS) EXP. FROM 6/11/05 TO 6/11/05 YOU HAVE 405 POINTS) HELP |
#2
|
|||
|
|||
1. Don't use all caps, it is considered rude (shouting) and it is harder to
read Try =SUMIF(A2:A100,"="&DATE(6,11,5),B2:B100) adapt the cell ranges to fit accordingly -- Regards, Peo Sjoblom (No private emails please) "RODRODROD" wrote in message ... COLUMN "A" HAS DATES IN IS AND COLUMN "E" HAS POINTS FOR THAT DATE. A E 6/11/05 80 6/05/05 100 6/11/05 95 6/05/05 73 6/11/05 230 "I WANT TO PUT ANYWHERE ON THE SPREADSHEET (FROM _____ TO _______ YOU X POINTS) EXP. FROM 6/11/05 TO 6/11/05 YOU HAVE 405 POINTS) HELP |
#3
|
|||
|
|||
If you really meant from to you might want either
=SUMPRODUCT(--(A2:A100=DATE(6,5,5)),--(A2:A100<=DATE(6,11,5)),B2:B100) or =SUMIF(A2:A100,"="&DATE(6,5,5),B2:B100)- SUMIF(A2:A100,""&DATE(6,11,5),B2:B100) that would give you from to -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... 1. Don't use all caps, it is considered rude (shouting) and it is harder to read Try =SUMIF(A2:A100,"="&DATE(6,11,5),B2:B100) adapt the cell ranges to fit accordingly -- Regards, Peo Sjoblom (No private emails please) "RODRODROD" wrote in message ... COLUMN "A" HAS DATES IN IS AND COLUMN "E" HAS POINTS FOR THAT DATE. A E 6/11/05 80 6/05/05 100 6/11/05 95 6/05/05 73 6/11/05 230 "I WANT TO PUT ANYWHERE ON THE SPREADSHEET (FROM _____ TO _______ YOU X POINTS) EXP. FROM 6/11/05 TO 6/11/05 YOU HAVE 405 POINTS) HELP |
#4
|
|||
|
|||
Oops! Change the DATE part to
DATE(5,6,5) and DATE(5,6,11) -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... If you really meant from to you might want either =SUMPRODUCT(--(A2:A100=DATE(6,5,5)),--(A2:A100<=DATE(6,11,5)),B2:B100) or =SUMIF(A2:A100,"="&DATE(6,5,5),B2:B100)- SUMIF(A2:A100,""&DATE(6,11,5),B2:B100) that would give you from to -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... 1. Don't use all caps, it is considered rude (shouting) and it is harder to read Try =SUMIF(A2:A100,"="&DATE(6,11,5),B2:B100) adapt the cell ranges to fit accordingly -- Regards, Peo Sjoblom (No private emails please) "RODRODROD" wrote in message ... COLUMN "A" HAS DATES IN IS AND COLUMN "E" HAS POINTS FOR THAT DATE. A E 6/11/05 80 6/05/05 100 6/11/05 95 6/05/05 73 6/11/05 230 "I WANT TO PUT ANYWHERE ON THE SPREADSHEET (FROM _____ TO _______ YOU X POINTS) EXP. FROM 6/11/05 TO 6/11/05 YOU HAVE 405 POINTS) HELP |
#5
|
|||
|
|||
On Sat, 11 Jun 2005 19:03:40 -0700, "Peo Sjoblom" wrote:
Oops! Change the DATE part to DATE(5,6,5) and DATE(5,6,11) The OP did not specify the century; but I should point out that your formula gives a date of 1905-June-05 on my machine. And entering his posted dates into my worksheet would give a year of 2005. The DATE worksheet function: If year is between 0 (zero) (4 for the 1904 date system) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108). --ron |
#6
|
|||
|
|||
LOL! I guess I am too tired today()
-- Regards, Peo Sjoblom (No private emails please) "Ron Rosenfeld" wrote in message ... On Sat, 11 Jun 2005 19:03:40 -0700, "Peo Sjoblom" wrote: Oops! Change the DATE part to DATE(5,6,5) and DATE(5,6,11) The OP did not specify the century; but I should point out that your formula gives a date of 1905-June-05 on my machine. And entering his posted dates into my worksheet would give a year of 2005. The DATE worksheet function: If year is between 0 (zero) (4 for the 1904 date system) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) | |||
Trying to create repeating dates and then sort column by date | Excel Worksheet Functions | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions |