calculating on dates

[Alex H]

Hi,I have a year that runs from 1st august to 31st july. I have a field
which contains a value to date. Can someone tell me the best way to
calculate a staright line value forecast for the whole year, based on todays
date?

Thanmks
A

 

[Rob Oldfield]

Where's the current value?

 

[Alex H]

Ina report - its called VTD

Alex

Where's the current value?

 

[Rob Oldfield]

The best place to do it will be in a calculated field in a query. Something
like...

Forecast: fnForecast([vtd])

where that function will be something like...

function fnForecast(vtd as double)
if month(date())>7 then
fnforecast=365*vtd/datediff("d",dateserial(year(date()),8,1),date())
else
fnforecast=365*vtd/datediff("d",dateserial(year(date())-1,8,1),date())
endif
end function

Completely untested, but should set you off on the right lines I think.
Basically just calculate how many days it is since the last 1st August, then
the result is vtd*(365/that_number_of_days). I don't know if you want to
take leap years into account.