Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates to months and calculating values for their months
I want to put dates to their months for coloumn A and work out the
monthly totals of column B according to month. Not using a pivot table. A B Date Litres 01-Jan-03 4185 05-Jan-03 4132 14-Jan-03 4132 01-Feb-03 4132 01-Mar-03 4616 01-Apr-03 5223 i would like the output to be as follows: A B jan-03 feb-03 subtract jan-03/number of days in jan * 1000 feb-03 mar-03 subract feb-03/number of days in feb * 1000 The problem i have is repeating months in cells fill down A B jan-03 xxxx jan-03 xxxx feb-03 xxxx Please help or suggest any ideas, i could do this in a pivot table but i need to automate macros for this. Thanks Lai |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates to months and calculating values for their months
=Sumif(A:A,"=01-Jan-03",B:B)-Sumif(A:A,"=01-Feb-03",B:B)
will get the sum for the month of Jan If you want the count of rows with a Jan-03 entry =COUNTIF(A:A,"=01-Jan-03")-COUNTIF(A:A,"=01-Feb-03") You can use these same formulas in code (demonstrated from the immediate window:) ? Application.Sumif(Columns(1),"=01-Jan-03",Columns(2))- _ Application.Sumif(Columns(1),"=01-Feb-03",Columns(2)) 12449 Hopefully this is something you can use. I can't say I totally understand your question. -- Regards, Tom Ogilvy "jigsaw2" wrote in message ... I want to put dates to their months for coloumn A and work out the monthly totals of column B according to month. Not using a pivot table. A B Date Litres 01-Jan-03 4185 05-Jan-03 4132 14-Jan-03 4132 01-Feb-03 4132 01-Mar-03 4616 01-Apr-03 5223 i would like the output to be as follows: A B jan-03 feb-03 subtract jan-03/number of days in jan * 1000 feb-03 mar-03 subract feb-03/number of days in feb * 1000 The problem i have is repeating months in cells fill down A B jan-03 xxxx jan-03 xxxx feb-03 xxxx Please help or suggest any ideas, i could do this in a pivot table but i need to automate macros for this. Thanks Lai |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating months between dates | Excel Worksheet Functions | |||
Help Calculating Partial Months between 2 dates. | Excel Worksheet Functions | |||
Calculating Years and Months Between Dates | Excel Discussion (Misc queries) | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Calculating Dates in Terms of Months | Excel Worksheet Functions |