I would like to establish a quantity in my spreadsheet for items that fall between today's date and the date 30 days in the future, so that I can count them for charting . Has anyone got any idea how to do this?
lost between 2 dates
-
-
Hi toologre,
Welcome to the board
One way is to use the SUMPRODUCT-function:
Let A-column consist of dates and B-column of values:
In order to get the total amount i B-column:
=SUMPRODUCT((A2:A36>=DATEVALUE(TEXT(NOW(),"DD-MM-YYYY")))*(A2:A36<=(DATEVALUE(TEXT(NOW()+30,"DD-MM-YYYY"))))*B2:B36)
In order to get the number of items in A-column that falls between the dates:
=SUMPRODUCT((A2:A36>=DATEVALUE(TEXT(NOW(),"DD-MM-YYYY")))*(A2:A36<=(DATEVALUE(TEXT(NOW()+30,"DD-MM-YYYY"))))*A2:A36)
Kind regards,
Dennis -
Hi Toologre
Another method that you might find useful is the Database Functions. If you download the Workbook below it should give you some ideas.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!