Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prorating calculations in Excel
After spending much time trying to find a nice way to calculate a decimal
value for number of months for use in protrating calculations I've come up with this fairly simple and short way. Compared to what I've seen on the net this seems quite simple, as long as you have the extra excell plugin added to get the extra functions (if needed). so... IF a date is in CELL C2 and the end date is "31/03/04" as seen in formula... DATEDIF(C2,"31/03/04","M")+(DATEDIF(C2,EOMONTH(C2,0),"D")+1)/TEXT(EOMONTH(C2 ,0),"DD") DATEDIF(C2,"31/03/04","M") ,,,,is the # of full months to date (im assuming an end of month date) DATEDIF( C2,EOMONTH(C2,0),"D" ) + 1 ,,, is the # of days let in the month +1 since I want to count the starting day. TEXT(EOMONTH(C2,0), "DD") ,,,, is a quick way of getting the total number of days in the start month. Using CEILING( BLABLABLA, 0.01) you can get some rounding the way you want... EOMONTH() may require a plugin added from the plugin menu of excel. [A LONG example with error checking] =IF( ISERROR( CEILING( DATEDIF(C2,"31/03/04","M")+(DATEDIF(C2,EOMONTH(C2,0),"D")+1)/TEXT(EOMONTH(C2 ,0),"DD"), 0.01) ), "???", CEILING( DATEDIF(C2,"31/03/04","M")+(DATEDIF(C2,EOMONTH(C2,0),"D")+1)/TEXT(EOMONTH(C2 ,0),"DD"), 0.01) ) ---bla email: [danielm at citenet dot net ]dot why are you reading this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with calculations in Excel | Excel Discussion (Misc queries) | |||
DEG MIN SEC CALCULATIONS IN EXCEL. | Excel Worksheet Functions | |||
calculations in excel | Excel Discussion (Misc queries) | |||
calculations in MS excel | Excel Worksheet Functions | |||
calculations in excel | Excel Worksheet Functions |