Results 1 to 15 of 16
Thread: date query (Excel XP)

20030828, 15:00 #1
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
date query (Excel XP)
Can anyone help me with a 'simple' formula?
I have a list of dates in the format of 12/7/2003 etc which I want to analyse to have say quarterly totals. For example to gather all costs of a certain type which have a January to March date. This would allow me to create a summary for any given period. I have only the 1 column to work with. The dates and the other expenses information is sent to me and I canot change the format.
I tried a "sum if" formula such as =IF(A1>1/7/2003<30/7/2003, SUM(D),"") but it doesn't seem to work using 'Greater than' and 'less than' in the same formula.
(The dates are in coumn A and the expenses are in column D)
Any help appreciated.
Dave

20030828, 15:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: date query (Excel XP)
There are two problems with your formula:
 Excel interprets 1/7/2003 as a division: "1 divided by 7 divided by 2003". You can get around this by putting the dates into worksheet cells an referring to these cells, or by using the DATE worksheet function: DATE(2003,7,1) returns the date value of the 1st of July, 2003.
 You can't put two conditions into one the way you try. You can combine conditions by using the AND function (and there are other methods too).
If you put 1/7/2003 in cell B1 and 20/7/2003 in cell B2, you can use
=IF(AND(A1>B1,A1<B2),SUM"(D),"")
If you'd rather use DATE:
=IF(AND(A1>DATE(2003,7,1),A1<DATE(2003,7,30)),SUM( D),"")

20030828, 15:41 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
First, the method you are using will not work if you have dates from more than one year. Second, you will need to use array formulas (you hold down the Shift and Ctrl keys when you press enter for the formula) to do what you want. If the dates are in A1:A18, and the Costs are in B1:B18, then the following arrar formula will calculate the costs for each quarter, and they work for dates in any year:
<pre>=SUM((MONTH(A1:A18)>0)*(MONTH(A1:A18)<=3)*B1: B18)
=SUM((MONTH(A1:A18)>3)*(MONTH(A1:A18)<=6)*B1:B18)
=SUM((MONTH(A1:A18)>6)*(MONTH(A1:A18)<=9)*B1:B18)
=SUM((MONTH(A1:A18)>9)*(MONTH(A1:A18)<=12)*B1:B18)
</pre>
Legare Coleman

20030828, 19:20 #4
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Many thanks for pointing out the conflict in my date formula. I understand what you say about the interpretation of the division. However, I only have the one column of cells with the dates and these are mixed transactions for the months passed. I would add more dates/expenses as the months go by. Are you saying I have to adda nother date column and somehow type in another date to refer to? I a still abit confues (thick as well). I attach a sample of my sheet. Can you possibly take a look.
Many thanks,
Dave

20030828, 19:51 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: date query (Excel XP)
I think you can do this without formulas. A pivot table seems ideal. I created a pivot table based on the data, with Item as row field, Date as column field and Sum of Amount as table field. Then I rightclicked the Date field and specified that it had to be grouped by quarter; then I dumped the Date field itself. See attached workbook (with Dutch captions, so you'll have to recreate the pivot table from scratch)

20030829, 18:09 #6
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Hans,
that was very useful indeed. I have never used pivot tables, now I see what they do. Thanks.
One thing, I can see you have the quarters inserted, but mine only shows transaction dates. i can rename the headings but how do I get the spreadsheet/pivot table to work out what goes into what quarter i.e. January to March. I can't see how you got the table to do that. If I simply turn off the dates they aren't calculated. . .
Dave

20030829, 19:20 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: date query (Excel XP)
The source table contains transaction dates. When I created the pivot table, each transaction date was listed separately. I then right clicked the gray "Date" field button and selected Groyp and Outline > Group... (don't know the exact menu options in English), and selected Quarters, then clicked OK. Excel takes care of grouping the JanMar data together etc. Next, I dragged the grey Date field button onto the Pivot Field List to remove it from the pivot table, so that only the quarters remained.

20030830, 05:23 #8
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Legare
I tried your method and it worls fine. Can you suggest a way to extend this by including the category of the expense. say for example I have "Withdrawl", "Electricity" etc in column B can I analyse say the quarter expense for each specific category as well as the quarter?
Dave

20030830, 05:25 #9
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Hans,
thanks again. Yes I found initially I did not get the dates To From box but after starting again I found it. However, this type of table treats eachof the "Withdrawl" entries as seperate items when in reality all withdrawls should be grouped together. It is so close but not quite. . .
Am I able to group common items by some sort of description that looks for say only a specific word in the item description?
Dave

20030830, 07:38 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: date query (Excel XP)
You can group items by selecting them (use click and Ctrlclick), then rightclick, select Group and Outline  Group. But you have to repeat that for each set of items that has to be grouped. It might be better to add a new column (which might be made invisible after poputating it) that contains the item category to be used in the pivot table.

20030830, 13:22 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
If the Date is in A1:A18, and the Category is in B1:B18, and the Cost is in C1:C18, then the following formula should give you what you want for "Withdrawal":
<pre>=SUM((MONTH(A1:A18)>0)*(MONTH(A1:A18)<=3)*(B1 :B18="Withdrawal")*C1:C18)
=SUM((MONTH(A1:A18)>3)*(MONTH(A1:A18)<=6)*(B1:B18= "Withdrawal")*C1:C18)
=SUM((MONTH(A1:A18)>6)*(MONTH(A1:A18)<=9)*(B1:B18= "Withdrawal")*C1:C18)
=SUM((MONTH(A1:A18)>9)*(MONTH(A1:A18)<=12)*(B1:B18 ="Withdrawal")*C1:C18)
</pre>
Legare Coleman

20030831, 13:29 #12
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Legare,
Fantastic, that is exactly what I wanted. many many thanks.
Just one glitch that I could live with, but nice to resolve, If the item description differs such as "withdrawl 2358749" followed by "Withdrawl 2358750" (slightly different) is there a way of using a wildcard to add any similar text? otherwise I cannot include withdrawls in my summary as the serial numbers used by the bank will invalidate it. I tried withdrawl* but it does not work. . .
Dave

20030831, 22:21 #13
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
One way would be to do something like this:
<pre>=SUM((MONTH(A1:A18)>0)*(MONTH(A1:A18)<=3)*(LE FT(B1:B18,9)="Withdrawl")*C1:C18)
=SUM((MONTH(A1:A18)>3)*(MONTH(A1:A18)<=6)*(LEFT(B1 :B18,9)="Withdrawl")*C1:C18)
=SUM((MONTH(A1:A18)>6)*(MONTH(A1:A18)<=9)*(LEFT(B1 :B18,9)="Withdrawl")*C1:C18)
=SUM((MONTH(A1:A18)>9)*(MONTH(A1:A18)<=12)*(LEFT(B 1:B18,9)="Withdrawl")*C1:C18)
</pre>
Legare Coleman

20030901, 14:42 #14
 Join Date
 Jul 2002
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: date query (Excel XP)
Legare,
excellent! Many thanks again, that is just right.
I realise what a lot I have to learn.
I thought the * denoted multiplication, so what significance does it carry in your formulas?
Dave

20030901, 14:49 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: date query (Excel XP)
Legare is not online at the moment, so I'll fill in for him. The * does denote multiplication. Legare's formulas make clever use of the fact that in formulas, TRUE = 1 and FALSE = 0. By multiplying conditions, you get TRUE only if both conditions are TRUE, so multiplying conditions is equivalent to combining them with AND.
<table border=1><td>Condition 1</td><td>Condition 2</td><td>Condition 1 * Condition 2</td><td>TRUE = 1</td><td>TRUE = 1</td><td>TRUE = 1</td><td>TRUE = 1</td><td>FALSE = 0</td><td>FALSE = 0</td><td>FALSE = 0</td><td>TRUE = 1</td><td>FALSE = 0</td><td>FALSE = 0</td><td>FALSE = 0</td><td>FALSE = 0</td></table>