I know how to calculate the formula in excel, however not sure how to calculate the Percentage of Year complete in a Text Box for my report.
Our Fiscal Year 2019 started 10/1/2018 and ends 9/30/2019. Any help is appreciated.
Regards
Gene
.
I know how to calculate the formula in excel, however not sure how to calculate the Percentage of Year complete in a Text Box for my report.
Our Fiscal Year 2019 started 10/1/2018 and ends 9/30/2019. Any help is appreciated.
Regards
Gene
.
Base calculation on current date? Consider:
Choose(Month(Date()), 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3) / 12
Of course, that will not account for partial month. March 1 will return same result as March 31.
What is the Excel formula?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
To what level of accuracy ? Hours, Days, weeks, Months?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Actually here's a function that takes a date and returns the period based on days.
Hopefully self explanatory
Code:Function fnPercFiscalYear(dDate As Variant) As Single ' Returns the percentage of days through a fiscal year ' You can change the start date by adjusting the constant value ' Usage : fnPercFiscalYear(#23/12/2018#) would return 22.8022 Const FYStartMth As Integer = 10 ' October, change as required Dim dStart As Date Dim dEnd As Date Dim dYearDays As Integer Dim dDaySofar As Integer If Not IsDate(dDate) Or IsNull(dDate) Then fnPercFiscalYear = 0 Exit Function End If ' Get current fiscal year start and end dates based around FYStartMth Oct. ' Doing it this ways ensures accurracy for leap years. If DatePart("m", Date) > FYStartMth - 1 Then dStart = DateValue(FYStartMth & "/" & DatePart("yyyy", Date)) Else dStart = DateValue(FYStartMth & "/" & DatePart("yyyy", Date) - 1) End If dEnd = DateAdd("yyyy", 1, dStart) - 1 dYearDays = DateDiff("d", dStart, dEnd) dDaySofar = DateDiff("d", dStart, dDate) fnPercFiscalYear = dDaySofar / dYearDays * 100 'Remove the *100 to get a fractional value End Function
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Minty,
Looking at it in terms of days past and then convert to percentage. Here is the excel formula
=YEARFRAC("2016-12-31","2017-01-31",1)
Will try your VBA function
Thank you
Gene