Results 1 to 5 of 5
  1. #1
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Percentage of Year Complete Calculation

    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
    .

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  5. #5
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to extract month and year from a complete date?
    By Tbernardazzi in forum Access
    Replies: 1
    Last Post: 07-06-2016, 08:57 PM
  2. Percentage calculation using two tables
    By vthompson9100@gmail.com in forum Queries
    Replies: 8
    Last Post: 04-16-2015, 02:18 PM
  3. Percentage calculation
    By CS_10 in forum Reports
    Replies: 36
    Last Post: 04-09-2014, 12:29 PM
  4. Replies: 1
    Last Post: 08-12-2011, 10:39 AM
  5. Percentage Calculation in a Query
    By Lynn in forum Queries
    Replies: 1
    Last Post: 07-16-2010, 11:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums