鉴于4-5-4日历和日期，我如何确定该日期的财政周？

1 回答

• 1

  我认为第一步是找到输入日期的2月1日的星期几 .

  接下来，找到FY的第一天，这是基于2月1日的一周 . 如果是星期一，星期二或星期三，那么FY的第一天是1月的最后一个星期日 . 否则，FY的第一天是1月的第一个星期日 .

  接下来，确定输入日期是在该FY还是之前 .

  然后，如果输入日期是前一个FY，那么获取该FY的第一天 .

  接下来，计算从FY的第一天到输入日期的天数 . 除以7，向上舍入到下一个整数 . 那是FY周 .

  那时，我将知道输入日期的FY年和FY周，并且可以返回它 .

  更新：

  这就是我所拥有的;它适用于我的测试：

  Public Function ConvertDateToRawFYWeek(convert_date As Date) As String
  'Fiscal year starts on either:
      'the last Sunday in January (if Feb 1st is a Mon, Tue, or Wed), OR:
      'the first Sunday in February (if Feb 1st is Thur, Fri, Sat, or Sun).
  
      Dim iCalendarYearOfInputDate As Long, iInputMonth As Long, iInputDay As Long, iTmpYear As Long
      Dim iFebFirstOfTmpYear As Long, strFebFirstWeekdayOfTmpYear As String
      Dim iFirstDayofFYOfTmpYear As Long
      Dim iFiscalYearOfInputDate As Long
      Dim iDayOfInputDate As Long
      Dim iDayOfFY As Long, iWeekOfFY As Long, strWeekOfFY As String
      Dim bDone As Boolean
  
      iCalendarYearOfInputDate = Year(convert_date)
      iInputMonth = Month(convert_date)
      iInputDay = Day(convert_date)
      iDayOfInputDate = CLng(DateValue(convert_date))
  
  
      bDone = False 'init.
      iTmpYear = iCalendarYearOfInputDate 'init.
      Do
  
              '***get the day of the week of feb 1st of tmp date's year:
              iFebFirstOfTmpYear = DateSerial(iTmpYear, 2, 1)
              strFebFirstWeekdayOfTmpYear = Format(iFebFirstOfTmpYear, "DDDD")
  
              '***get the first day of the FY of the tmp date's year:
              Select Case strFebFirstWeekdayOfTmpYear
                  Case "Monday"
                      'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 31st:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 1
                  Case "Tuesday"
                      'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 30th:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 2
                  Case "Wednesday"
                      'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 29th:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 3
                  Case "Thursday"
                      'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 4th:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 3
                  Case "Friday"
                      'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 3rd:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 2
                  Case "Saturday"
                      'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 2nd:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 1
                  Case "Sunday"
                      'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 1st:
                      iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear
              End Select
  
              '***get the fiscal year of the input date:
              If iDayOfInputDate >= iFirstDayofFYOfTmpYear Then
                  iFiscalYearOfInputDate = iTmpYear
                  bDone = True
              Else
                  iTmpYear = iTmpYear - 1 'loop again.
              End If
  
      Loop Until bDone
  
  
      '***count the days from that first day of the FY, to the day of the input date.
      'Divide by 7, rounding UP to the next integer. That is the FY week.
      iDayOfFY = iDayOfInputDate - iFirstDayofFYOfTmpYear
      iWeekOfFY = Round((iDayOfFY / 7) + 0.50000000000001) 'round up to next integer.
      strWeekOfFY = Format(iWeekOfFY, "00")
  
      strFY = Format(iTmpYear, "0000")
  
      ConvertDateToRawFYWeek = strFY & strWeekOfFY
  
  End Function
  

  回复于 2024-05-02T11:31:16+08:00