calculating on dates

  • Thread starter Thread starter Alex H
  • Start date Start date
A

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
 
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.
 
Back
Top