Formula for current date calculations 1

[nb4884]

Hi All,

I have data for a project as below:
Start of the month(date) Monthly Usage(data in seconds)
03/01/2011 2000
04/01/2011 1000
05/01/2011 3000
and so on
10/01/2011 2000
11/01/2011 3000

I have to calculate the usage on fiscal year i.e (04/01/2011- 03/31/2012 is FY 2012)
Please suggest me a formula that would calculate the total Usage for ongoing FY till currentdat.
That is a formula that would sum up all the usage from 04/01/2011 to currentdate. i.e 1k+3K..+2K+ portion of 3K till currentdate.

Thanks

 

[lbass]

I don't know what you mean. There are no averages here. I tested the formula and it prorates the monthly usage to reflect the amount for the balance of the current month, and shows total amounts for future months.

Or are you saying that it is off by one day (currentdate)? If so, then add a 1.

-LB

 

[nb4884]

It does not show me the balance of the current month.

Only shows data starting from 12/1 onwards.

Thanks

 

[lbass]

I tested it and it works here, so you'd better show me the actual formula you used.

-LB

 

[nb4884]

I used the same formula as :

if year({table.date}+184) = year(currentdate+184) and
{table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
(
{table.monthlyusage}/
day(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)*
(
day
(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)-day(currentdate)
)
)
)



Pls see there is no condition for = current month. Its either greater than or not eqaul to.

Thanks

 

[lbass]

I think I know the problem. Your dates are always just the first of the month, right? Change the formula to:

if year({table.date}+184) = year(currentdate+184) then
(
if {table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage}
) else
(
if month({table.date})= month(currentdate) then
(
{table.monthlyusage}/
day(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)*
(
day
(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)-day(currentdate)
)
)
)
)

-LB

 

[nb4884]

Thanks LB

You are awesome !!!