Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Between 2 Calendar dates
I have 2 worksheets called 1) Data 2) between_2_dates.
The sheet called Data holds all the information. I have the following formula in cell B2 of sheet between_2_dates: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01))) I am using this calendar control to enter a date in cell D2 and I2 of sheet between_2_dates: http://www.rondebruin.nl/calendar.htm This works fine, but I need to include these 2 dates in the formula above, so DATE(2003,01,01) will be replaced and use the date in cell D2 and DATE(2008,01,01) will be replaced and use the date in cell I2. What would be the complete formula to do this? Many thanks for any help with this. -- Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Between 2 Calendar dates
If D2 and I2 are both dates, then you can substitute them directly,
i.e.: =SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<*=I2)) Hope this helps. Pete On Jun 21, 12:44 pm, Richard wrote: I have 2 worksheets called 1) Data 2) between_2_dates. The sheet called Data holds all the information. I have the following formula in cell B2 of sheet between_2_dates: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<*=DATE(2008,01,01))) I am using this calendar control to enter a date in cell D2 and I2 of sheet between_2_dates: http://www.rondebruin.nl/calendar.htm This works fine, but I need to include these 2 dates in the formula above, so DATE(2003,01,01) will be replaced and use the date in cell D2 and DATE(2008,01,01) will be replaced and use the date in cell I2. What would be the complete formula to do this? Many thanks for any help with this. -- Richard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Between 2 Calendar dates
Do exactly what you say in your message: replace the dates with cells. Your new
formula would be: =SUMPRODUCT(--(Data!$A$1:$A$10000=d2),--(Data!$A$1:$A$10000<=i2)) -- Regards, Fred "Richard" wrote in message ... I have 2 worksheets called 1) Data 2) between_2_dates. The sheet called Data holds all the information. I have the following formula in cell B2 of sheet between_2_dates: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01))) I am using this calendar control to enter a date in cell D2 and I2 of sheet between_2_dates: http://www.rondebruin.nl/calendar.htm This works fine, but I need to include these 2 dates in the formula above, so DATE(2003,01,01) will be replaced and use the date in cell D2 and DATE(2008,01,01) will be replaced and use the date in cell I2. What would be the complete formula to do this? Many thanks for any help with this. -- Richard |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Between 2 Calendar dates
=SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=I2))
Richard wrote: I have 2 worksheets called 1) Data 2) between_2_dates. The sheet called Data holds all the information. I have the following formula in cell B2 of sheet between_2_dates: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01))) I am using this calendar control to enter a date in cell D2 and I2 of sheet between_2_dates: http://www.rondebruin.nl/calendar.htm This works fine, but I need to include these 2 dates in the formula above, so DATE(2003,01,01) will be replaced and use the date in cell D2 and DATE(2008,01,01) will be replaced and use the date in cell I2. What would be the complete formula to do this? Many thanks for any help with this. -- Richard -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Between 2 Calendar dates
Thank you everyone, that works fine.
I was trying to this <=DATE(D2) and wondered why it wasn't working. -- Richard "Dave Peterson" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=I2)) Richard wrote: I have 2 worksheets called 1) Data 2) between_2_dates. The sheet called Data holds all the information. I have the following formula in cell B2 of sheet between_2_dates: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2003,01,01)),--(Data!$A$1:$A$10000<=DATE(2008,01,01))) I am using this calendar control to enter a date in cell D2 and I2 of sheet between_2_dates: http://www.rondebruin.nl/calendar.htm This works fine, but I need to include these 2 dates in the formula above, so DATE(2003,01,01) will be replaced and use the date in cell D2 and DATE(2008,01,01) will be replaced and use the date in cell I2. What would be the complete formula to do this? Many thanks for any help with this. -- Richard -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP calendar dates | Excel Discussion (Misc queries) | |||
Dates from Julian calendar | Excel Discussion (Misc queries) | |||
Calendar/Dates Help | Excel Discussion (Misc queries) | |||
Dates - Calendar | Excel Discussion (Misc queries) | |||
Linking dates with a calendar... | Excel Worksheet Functions |