Assuming the value is in A1:A100 and the dates are in B1:B100
will return the sum of the values fro January 2004
I have a list of records say with an amount and a date. How can I analyse the list based on the date range i.e. all records between say date 1 and date 2 total x. I would like the total to be returned to a single cell
Here is one way
Where $A$1:$A$100 has the dates and $B$1:$B$100 the numbers to SUM. D1= date1 and D2 = date2
Or, use the more efficient DSUM like
Where A1 is the Date heading
E1 and F1 has the exact same heading as A1
E2 has >=11/11/04
F2 has <=12/12/04
Those are not dates & need converting first.
Try running this code on the range containing the text-dates
VB:Sub Convert_To_Date() 'converts a range of text dates to excel dates Dim cdata As Range, cell As Range Set cdata = Application. _ InputBox("Select Your Range to Convert", Type:=8) For Each cell In cdata.Cells cell.Value = DateValue(cell.Value) Next cell End Sub
You could also use
then Copy and Edit>Paste Special - Values over the top of the orginals. Then format as required.
I'm not familiar with code at all and have no idea how to do it. Is there anyway in Excel that I can conver it. When I go into the individual cell and just delete the ' Excel can read it. I just need a quick way of doing this for a few thousand records. A macro perhaps? I don't know.
Brian, Will's method is a macro
Push Alt+F11 and go to Insert>Module and paste in the code. Now go back to Excel and push Alt+F8 and selected Convert_To_Date and click Run
No trying to be smart, but I would suggest changing your Assumed Experience: down from Well above average
That's what I gave you.
OK, press ALT+F11 from your worksheet
This opens the Visual Basic Editor.
Paste the code I gave you in the new module.
Then you can just run the macro from your worksheet using Tools|Macros.
Or, use Dave's formula based suggestion...
There are currently 2 users browsing this thread. (0 members and 2 guests)