اعداد هفته در اکسل

مقدمه
بسیاری از برنامه ها و سازمان ها از شماره هفته تاریخ برای نوعی شناسایی، طبقه بندی یا قالب بندی استفاده می کنند. با این حال، آنچه یک عدد هفته را تشکیل می‌دهد، به‌ویژه زمانی که هفته اول شروع می‌شود، همیشه از یک سازمان یا برنامه به سازمان دیگر یکسان نیست. این صفحه فرمول‌های کاربرگ و رویه‌های VBA را شرح می‌دهد که می‌توانید از آنها برای محاسبه تعداد هفته به روش‌های مختلف استفاده کنید.

فرمول ها و توابع شرح داده شده در این صفحه با تعاریف زیر از اولین روز هفته اول سال کار می کنند:
■ اولین روز هفته اول ژانویه همان سال است، صرف نظر از اینکه چه روزی از هفته ممکن است باشد.
■ اولین روز از هفته 1 اولین رخداد برخی از روزهای مشخص هفته است. برای مثال، ممکن است هفته اول را برای شروع اولین دوشنبه سال تعریف کنید.
■ روز اول هفته اول یک تاریخ مشخص است. به عنوان مثال، ممکن است تعریف کنید که هفته اول همیشه از اول ماه مه سال شروع می شود.
■ اولین روز از هفته 1 اولین اتفاق یک روز از هفته پس از اولین وقوع یک روز دیگر از هفته است. به عنوان مثال، ممکن است تعریف کنید که هفته اول از اولین دوشنبه پس از اولین پنجشنبه سال آغاز می شود.
■ اولین روز هفته اول با تعریف سازمان بین المللی استاندارد (ISO) مشخص شده است.

عدد مطلق هفته
این ساده ترین روش است. فرض بر این است که هفته 1 از اول ژانویه شروع می شود، صرف نظر از اینکه چه روزی از هفته ممکن است باشد. فرمول این است:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

که در آن StartDate تاریخی است که شماره هفته آن باید برگردانده شود.

کد VBA برای این تابع:

Public Function WeekNumberAbsolute(DT As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function

شماره هفته اولین روز هفته
این روش به عنوان اولین روز هفته 1 از اولین رخداد یک روز مشخص از هفته استفاده می کند. برای مثال، ممکن است تعریف کنید که هفته اول از اولین دوشنبه سال شروع می شود. فرمول این است:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)

که در آن StartDate عدد هفته ای است که باید پیدا شود و DayOfWeek روز هفته است

کد VBA برای این تابع:

Public Function WeekNumberFromFirstDayOfWeek(DT As Date, DayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromFirstDayOfWeek
' This returns the week number of the date DT based on Week 1 starting
' on the first DayOfWeek of the year of DT.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YEAR(DT),1,1)),7))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WeekNumberFromFirstDayOfWeek = Int(((DT - DateSerial(Year(DT), 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function

شماره هفته از تاریخ

این روش از تاریخ خاصی به عنوان اولین روز هفته 1 استفاده می کند. فرمول این است:

=TRUNC(((TestDate-StartDate)+6)/7)+(WEEKDAY(TestDate)=WEEKDAY(StartDate))

که در آن TestDate تاریخی است که شماره هفته آن باید محاسبه شود و StartDate اولین روز هفته 1 را مشخص می کند.

کد VBA برای این تابع:

Public Function WeekNumberFromDate(DT As Date, StartDate As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDate
' This returns the week number base on Week 1 starting on StartDate.
'       =TRUNC(((DT-StartDate)+6)/7)+(WEEKDAY(DT)=WEEKDAY(StartDate))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   WeekNumberFromDate = Int(((DT - StartDate) + 6) / 7) + Abs(Weekday(DT) = Weekday(StartDate))
End Function

شماره هفته از اولین روز هفته به دنبال روز دیگر هفته

این روش به عنوان اولین روز هفته 1 از اولین رخداد یک روز مشخص از هفته استفاده می کند که پس از اولین وقوع یک روز دیگر از هفته است. برای مثال، ممکن است بخواهید اولین روز هفته، دوشنبه بعد از اولین پنجشنبه سال باشد. فرمول این است:

=TRUNC(((TestDate-((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7))))+6)/7)+
(WEEKDAY(TestDate)=WEEKDAY(((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7)))))

که در آن TestDate تاریخی است که تعداد هفته آن باید محاسبه شود. StartDayOfWeek تعداد روزهای هفته است که پس از آن BaseDate باید پیدا شود. BaseDayOfWeek شماره روزهای هفته از روز بعد از اولین وقوع BaseDayOfWeek است که هفته 1 باید شروع شود. به عنوان مثال، برای استفاده از اولین دوشنبه بعد از اولین پنجشنبه سال، BaseDayOfWeek باید 2 و StartDate باید 5 باشد.

کد VBA برای این تابع:

Public Function WeekNumberFromDayOfWeekDay(DT As Date, BaseDayOfWeek As VbDayOfWeek, _
    StartDayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDayOfWeekDay
' This returns the week number based on Week 1 starting on the first
' StartDayOfWeek following the first BaseDayOfWeek day of week in the
' year of DT. E.g., the week number of 8-March-2009 based on Week 1
' beginning on the first StartDayOfWeek = Wednesday following the first
' BaseDayOfWeek = Sunday of the year 2009. That is, find the first Sunday,
' go to the next Wednesday, and begin Week 1 on that Wednesday.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FirstDayOfYear As Long
Dim FirstDayOfYearWeekday As Long

FirstDayOfYear = DateSerial(Year(DT), 1, 1)
FirstDayOfYearWeekday = Weekday(FirstDayOfYear)

WeekNumberFromDayOfWeekDay = Int(((DT - ((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))) + 6) / 7) + _
        (Weekday(DT) = Weekday(((FirstDayOfYear + WSMod(BaseDayOfWeek - _
        FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))))

End Function

شماره هفته ISO

این روش از تعریف سازمان استاندارد بین المللی (ISO) از عدد هفته استفاده می کند. یک هفته ISO همیشه از دوشنبه شروع می شود و هفته ایزو 1 از دوشنبه اولین هفته سال که شامل پنج شنبه است شروع می شود. به طور متفاوت، ISO هفته 1 اولین هفته ای است (از دوشنبه شروع می شود) که حداقل چهار روز دارد.

توجه داشته باشید که ممکن است چند روز اول سال به هفته 52 یا هفته 53 برسند. اگرچه این ممکن است کاملاً غیر شهودی به نظر برسد، اولاً همه هفته های ISO از دوشنبه شروع می شوند و دوم اینکه اولی هفته باید شامل یک پنجشنبه باشد. به عنوان مثال، سال 2010 در روز جمعه، 1-ژانویه-2010 آغاز می شود. از آنجایی که این دیرتر از پنج شنبه است (یعنی 7 ژانویه 2010)، هفته شامل پنجشنبه نیست (یا به عبارت دیگر هفته حداقل چهار روز ندارد)، هفته ISO 1 از دوشنبه آغاز می شود. , 4-ژانويه-2010. روز قبل از شروع سال، 31-دسامبر-2009، در هفته ISO 53 سال قبل، 2009 قرار دارد. تا 4 ژانویه 2010، که همانطور که قبلا ذکر شد، دوشنبه هفته اول است که شامل یک پنجشنبه (یا به طور معادل، اولین هفته با چهار روز یا بیشتر) است.

کد VBA برای محاسبه شماره هفته ISO در زیر نشان داده شده است:

Public Function IsoWeekNumber(InDate As Date) As Long
    IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function

تابع WSMod

برای حفظ تقارن بین VBA و پیاده‌سازی فرمول توابع مختلف، کد از تابعی به نام WSMod استفاده می‌کند که مانند MOD اکسل کار می‌کند. این با اپراتور Mod VBA متفاوت است.

Private Function WSMod(Number As Double, Divisor As Double) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WSMod
' The Excel worksheet function MOD and the VBA Mod operator
' work differently and can return different results under
' certain circumstances. For continuity between the worksheet
' formulas and the VBA code, we use this WSMod function, which
' produces the same result as the Excel MOD worksheet function,
' rather than the VBA Mod operator.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WSMod = Number - Divisor * Int(Number / Divisor)
End Function