كيفية تحويل رقم الأسبوع إلى تاريخ والعكس في Excel؟

العمل مع التواريخ وأرقام الأسابيع في Excel هو حاجة شائعة في تحليلات الأعمال، تخطيط المشاريع، والتقارير. على سبيل المثال، قد ترغب في معرفة الأسبوع الذي يقع فيه تاريخ معين، أو تحديد نطاق التاريخ لرقم أسبوع محدد خلال سنة معينة. ومع ذلك، لا توفر Excel خيارات مضمنة مباشرة لتحويل أرقام الأسابيع إلى نطاقات تواريخ كاملة أو عكس العملية بسرعة. لمعالجة هذه الاحتياجات، يمكنك استخدام مجموعة متنوعة من الصيغ، حلول VBA، وميزات أخرى في Excel بناءً على متطلباتك الخاصة وحجم البيانات التي تريد معالجتها. فيما يلي عدة طرق عملية للتعامل مع هذه المهمة في Excel.

تحويل رقم الأسبوع إلى تاريخ باستخدام الصيغ

تحويل التاريخ إلى رقم الأسبوع باستخدام الصيغ

التحويل بين رقم الأسبوع والتاريخ باستخدام أكواد VBA

تحويل رقم الأسبوع إلى تاريخ باستخدام الصيغ

لنفترض أن لديك سنة معينة ورقم أسبوع مدخل في ورقة العمل (على سبيل المثال، 2015 في الخلية B1 و 15 في الخلية B2). قد ترغب في حساب تاريخ البداية الفعلي (الاثنين) وتاريخ النهاية (الأحد) لهذا الأسبوع. يمكن أن يكون هذا مفيدًا بشكل خاص في تخطيط الجداول الزمنية، إعداد ملخصات أسبوعية، أو الرجوع إلى فترات الإبلاغ الأسبوعية.

لحساب نطاق التاريخ للرقم الأسبوعي المحدد، يمكنك استخدام الصيغ التالية في Excel:

1. اختر خلية فارغة لعرض تاريخ البداية (هنا، الخلية B5). أدخل الصيغة التالية، ثم اضغط على مفتاح Enter. ستقوم الصيغة بإرجاع رقم تسلسلي يمثل التاريخ.

=MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1)

2. بالنسبة لتاريخ نهاية نفس الأسبوع (على سبيل المثال، في الخلية B6)، أدخل الصيغة التالية، ثم اضغط على Enter. ستقوم الصيغة بإرجاع الرقم التسلسلي ليوم النهاية للأسبوع المحدد.

=MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7)

ملاحظة: في الصيغ أعلاه، B1 هي الخلية التي تحتوي على السنة (على سبيل المثال، 2015)، وB2 تحتوي على رقم الأسبوع الذي تريد تحويله. قم بتعديل هذه المراجع حسب ورقة عملك الفعلية عند الحاجة.

3. تعيد الصيغ في البداية أرقامًا بدلاً من التواريخ المنسقة. لعرض تنسيق التاريخ الصحيح، حدد كلتا الخليتين اللتين تحتويان على الصيغ، ثم اذهب إلى الصفحة الرئيسية > تنسيق الأرقام القائمة المنسدلة > التاريخ القصير. سيقوم هذا بتحويل القيم إلى تواريخ قابلة للتعرف.

تحويل التاريخ إلى رقم الأسبوع باستخدام الصيغ

على العكس، قد ترغب في تحديد رقم الأسبوع الذي يقع فيه تاريخ معين. توفر Excel دالة WEEKNUM لهذا الغرض. هذا مفيد بشكل خاص عند تحليل بيانات الجدول الزمني، إنشاء تقارير أسبوعية، أو تتبع عمليات التسليم والفعاليات بالأسبوع.

1. اختر خلية فارغة لإخراج رقم الأسبوع. أدخل الصيغة التالية (بافتراض أن تاريخك موجود في الخلية B1):

=WEEKNUM(B1,1)

2. ثم اضغط Enter. هذه الصيغة تعيد رقم الأسبوع باعتبار الأحد أول أيام الأسبوع.

ملاحظات:

(1) في هذه الصيغة، B1 هي الخلية التي تحتوي على التاريخ الذي تريد تحويله.

(2) إذا كنت تفضل حساب الأسابيع بدءًا من الاثنين (شائع في نظام ISO للأسبوع)، استخدم هذه النسخة من الصيغة:

=WEEKNUM(B1,2)

التحويل بين رقم الأسبوع والتاريخ باستخدام أكواد VBA

في هذه المقالة، سنستعرض روتينين من VBA: أحدهما يقوم بتحويل رقم الأسبوع (والسنة) إلى نطاق التاريخ المقابل له، والآخر يحدد رقم الأسبوع ISO لأي تاريخ معطى.

تحويل رقم الأسبوع إلى نطاق التاريخ:

1. افتح محرر VBA بالضغط على Developer > Visual Basic. في النافذة التي تظهر، اضغط على Insert > Module والصق الكود أدناه في الوحدة:

Sub WeekNumberToDateRange()
    Dim YearNum As Long
    Dim WeekNum As Long
    Dim FirstDay As Date, LastDay As Date
    Dim Jan4 As Date
    YearNum = Application.InputBox("Enter the year:", "KutoolsforExcel", Year(Date), Type:=1)
    If YearNum < 1 Then Exit Sub
    WeekNum = Application.InputBox("Enter the week number:", "KutoolsforExcel", 1, Type:=1)
    If WeekNum < 1 Then Exit Sub
    Jan4 = DateSerial(YearNum, 1, 4)
    FirstDay = Jan4 - Weekday(Jan4, vbMonday) + 1
    FirstDay = FirstDay + (WeekNum - 1) * 7
    LastDay = FirstDay + 6
    MsgBox "Start date: " & Format(FirstDay, "yyyy-mm-dd") & vbCrLf & _
           "End date:   " & Format(LastDay, "yyyy-mm-dd"), _
           vbInformation, "KutoolsforExcel"
End Sub 

2. قم بتشغيل الماكرو باستخدام الزر. سيطلب منك السنة ورقم الأسبوع، ثم يعرض نطاق التاريخ المقابل في نافذة حوار.

تحويل التاريخ إلى رقم الأسبوع:

1. انسخ والصق الكود التالي الخاص بـ VBA في الوحدة:

Sub DateToWeekNumber()
 Dim InputDate As Date
 Dim WeekNum As Integer
 InputDate = Application.InputBox("Enter the date (yyyy-mm-dd):", "KutoolsforExcel", Date, Type:=2)
 WeekNum = WorksheetFunction.WeekNum(InputDate, 2)
 MsgBox "The week number is: " & WeekNum, vbInformation, "KutoolsforExcel"
End Sub  

2. بعد إدراج وتشغيل هذا الكود، أدخل تاريخك المستهدف عند المطالبة وسيعرض الماكرو رقم الأسبوع، مع اعتبار الاثنين بداية الأسبوع. يمكنك تعديل الكود عن طريق تغيير الوسيطة الثانية في WeekNum إلى 1 للأسبوع الذي يبدأ يوم الأحد.

مقالات ذات صلة:

كيفية حساب عدد أيام الأسبوع المحددة بين تاريخين في Excel؟

كيفية إضافة / طرح أيام / أشهر / سنوات من تاريخ في Excel؟