Projecting annual 'turnover' from a given date (need how many days between feature!)

[peskywinnets]

Hiya,

ok, still fairly new to access but loving the way it lets me arrange & drill down on my data (quite a revelation after working with excel for so many years )

I've worked out how to sum all my sales (turnover) from a given nominated date...but what I seek now is to project forward what the annual turnover will be (which will quickly give me a 'pulse' & indicate if I'm growing as my financial year progresses)

My financial year started on Feb 4 2014 ...I can easily sum all my sales from that date, but ideally what I'd like to do is divide that sum by the number of days elapsed (this will give the average turnover per day) & then project that forward to Feb 3 2015 (the last day of my financial year)

So is there anyway that access can work out the number of days that have elapsed between 4 feb & 'now' (to get the average) ....but more specifically also how many days between 'now' ....and Feb 3 2015 - I then can use the remaining number of days to multiply the average daily takins to give an indication of what my year end annual turnover will be


Or am I faced with having to do such calculations manually outside of access?

 

[spikepl]

http://www.techonthenet.com/access/functions/

 

[plog]

From your explanation, I believe you've overthought the calculation that needs to go into this. The end date is irrelevant to this calculation. Once you have the Average Turnover you can use that average to calculate the turnover over however long an interval you want.

AverageTurnover = SalesSum/ElapsedDays

You said you have the SalesSum, to get the ElapsedDays you use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to determine days between the start of your year and the last date of the data that went into SalesSum. With that you can determine the Turnover for whatever interval you want:

AnnualTurnover = AverageTurnover * 365
WeeklyTurnover = AverageTurnover * 7