Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I automatically split values over specified time periods?
Hi, I monitor the energy usage for my organisation and report quarterly. As
you will be aware, utility bills do not always come in nice and neat quarterly or monthly invoicing periods and I would really benefit from a way of automatically splitting amounts entered into quarters. e.g I enter 25 Dec 07 to 04 Feb 08 = 1000 kwh Rather than manually dividing myself, can I allocate 5 days usage to the Oct to Dec quarter and the rest to the Jan to Mar quarter? Any help would be greatly appreciated :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I automatically split values over specified time periods?
Hi,
I had to do something similar, this was the fastest technique: Let A & B be your quarter start and end date, X & Y the period covered on the electricity bill. You want to work out the overlap between these two periods (you can do the same for the next quarter after AB etc) The biggest the overlap can be is: A+1-B days (if A and B are the same it's 1 day, hence the +1) So, so far =(A+1-B) If the bill period ends before the quarter period then you need to knock theses days off. This is Max(B-Y,0), so we have =(A+1-B) - Max(B-Y,0) If the bill period starts after the quarter period then you need to knock these days off, this is Max(X-A,0)... This gives =(A+1-B)-Max(B-Y,0)-Max(X-A,0) As a final point, the whole lot must be at least 0 days, so this must be the final formula: =Max((A+1-B)-Max(B-Y,0)-Max(X-A,0),0) I hope that makes sense... "Grushenka" wrote: Hi, I monitor the energy usage for my organisation and report quarterly. As you will be aware, utility bills do not always come in nice and neat quarterly or monthly invoicing periods and I would really benefit from a way of automatically splitting amounts entered into quarters. e.g I enter 25 Dec 07 to 04 Feb 08 = 1000 kwh Rather than manually dividing myself, can I allocate 5 days usage to the Oct to Dec quarter and the rest to the Jan to Mar quarter? Any help would be greatly appreciated :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I automatically split values over specified time perio
Thanks Sam! :)
"Sam Wilson" wrote: Hi, I had to do something similar, this was the fastest technique: Let A & B be your quarter start and end date, X & Y the period covered on the electricity bill. You want to work out the overlap between these two periods (you can do the same for the next quarter after AB etc) The biggest the overlap can be is: A+1-B days (if A and B are the same it's 1 day, hence the +1) So, so far =(A+1-B) If the bill period ends before the quarter period then you need to knock theses days off. This is Max(B-Y,0), so we have =(A+1-B) - Max(B-Y,0) If the bill period starts after the quarter period then you need to knock these days off, this is Max(X-A,0)... This gives =(A+1-B)-Max(B-Y,0)-Max(X-A,0) As a final point, the whole lot must be at least 0 days, so this must be the final formula: =Max((A+1-B)-Max(B-Y,0)-Max(X-A,0),0) I hope that makes sense... "Grushenka" wrote: Hi, I monitor the energy usage for my organisation and report quarterly. As you will be aware, utility bills do not always come in nice and neat quarterly or monthly invoicing periods and I would really benefit from a way of automatically splitting amounts entered into quarters. e.g I enter 25 Dec 07 to 04 Feb 08 = 1000 kwh Rather than manually dividing myself, can I allocate 5 days usage to the Oct to Dec quarter and the rest to the Jan to Mar quarter? Any help would be greatly appreciated :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to create line chart with values not in all time periods? | Charts and Charting in Excel | |||
periods of time | New Users to Excel | |||
calculate time periods | Excel Worksheet Functions | |||
No. of days split into periods | Excel Discussion (Misc queries) | |||
periods of time in excel | Excel Programming |