كيف يمكنك التحقق مما إذا كان التاريخ يوافق عطلة رسمية، وحساب عدد الأيام مع استبعاد العطلات في Excel؟
على سبيل المثال، إذا كانت لديك قائمة تواريخ وتريد التحقق بسرعة مما إذا كانت تحتوي على أي عطلات رسمية، فكيف يمكنك تحقيق ذلك؟ في هذه المقالة، سأوضح لك الطرق الفعّالة لحساب جميع تواريخ العطلات الرسمية الأمريكية ضمن سنوات معيّنة، ثم التحقق بسهولة مما إذا كان تاريخٌ ما يوافق عطلةً رسميةً أمريكية في Excel.
- الجزء 1: حساب العطلات الرسمية ضمن سنوات معيّنة في Excel
- الجزء 2: التحقق مما إذا كان التاريخ عطلة رسمية في Excel
- الجزء 3: حساب عدد الأيام بين تاريخين باستثناء عطلات نهاية الأسبوع والعطلات الرسمية في Excel
الجزء 1: حساب العطلات الرسمية وفقًا للسنة المحددة في Excel
قبل التحقق مما إذا كان التاريخ يُعد عطلة رسمية، يجب عليك إدراج جميع العطلات الرسمية ضمن السنوات المحددة في Excel. ولذلك، فإن إعداد جدول كما في لقطة الشاشة التالية سيجعل مهمتك أكثر سهولةً وسلاسة.
هناك ثلاثة أنواع من العطلات الرسمية الأمريكية:
- (1) النوع الأول هو عطلة رسمية بتاريخ ثابت، مثل رأس السنة الميلادية الذي يصادف 1 يناير. ويمكنك بسهولة حساب تاريخ رأس السنة باستخدام الصيغة=DATE(Given Year,1,1);
- (2) النوع الثاني هو عطلة رسمية تقع في يوم ثابت من الأسبوع، مثل عيد الرئيس. ويمكنك بسهولة حساب موعد عيد الرئيس باستخدام الصيغة التالية: =DATE(Given Year,1,1)+14+CHOOSE(WEEKDAY(DATE(Given Year,1,1)),1,0,6,5,4,3,2)؛
- (3) أما النوع الأخير فهو عيد الذكرى، ويمكنك حسابه بسهولة باستخدام الصيغة=DATE(Given Year,6,1)-WEEKDAY(DATE(Given Year,6,6)).
لقد أدرجتُ هنا الصيغ اللازمة لحساب جميع العطلات الرسمية في الجدول التالي. ما عليك سوى إدخال كل صيغة في خليتها المخصصة، ثم الضغط على مفتاحEnter واحدةً تلو الأخرى.
| عطلة | خلية | الصيغ |
| يوم رأس السنة | C2 | =DATE(C1,1,1) |
| عيد مارتن لوثر كينغ جونيور | C3 | =DATE(C1,1,1)+14+CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2) |
| عيد الرئيس | C4 | =DATE(C1,2,1)+14+CHOOSE(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2) |
| عيد الذكرى | C5 | =DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6)) |
| عيد الاستقلال | C6 | =DATE(C1,7,4) |
| عيد العمال | C7 | =DATE(C1,9,1)+CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2) |
| عيد كولومبوس | C8 | =DATE(C1,10,1)+7+CHOOSE(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2) |
| عيد المحاربين القدامى | C9 | =DATE(C1,11,11) |
| عيد الشكر | C10 | =DATE(C1,11,1)+21+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5) |
| عيد الميلاد | C11 | =DATE(C1,12,25) |
ملاحظة:في الصيغ المذكورة في الجدول أعلاه، تمثّل الخلية C1 خلية المرجع التي تحتوي على السنة المحددة. وفي مثالنا هذا، تشير إلى السنة 2015، ويمكنك تغييرها حسب احتياجاتك.
باستخدام هذه الصيغ، يمكنك بسهولة حساب تواريخ العطلات الرسمية وفقًا للسنوات المحددة. راجع لقطة الشاشة أدناه:
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
الجزء 2: التحقق مما إذا كان التاريخ عطلة رسمية في Excel
بعد إدراج جميع تواريخ العطلات الرسمية لسنوات معيّنة، يمكنك بسهولة التحقق مما إذا كان تاريخٌ ما يوافق عطلة رسمية باستخدام الصيغ في Excel. لنفترض أن لديك قائمة تواريخ كما في لقطة الشاشة التالية، وسأعرض لك طرقًا تُمكّنك من إنجاز ذلك بكل يسر.
اختر خلية فارغة بجانب قائمة التواريخ، مثل الخلية B18، وأدخل الصيغة=IF(COUNTIF($C$2:$D$11,A18),«Holiday»,«No») فيها، ثم اسحب مقبض التعبئة (Fill Handle) إلى النطاق المطلوب. راجع لقطة الشاشة أعلاه:
ملاحظات:
(1) في الصيغة =IF(COUNTIF($C[[PH_20]]:$D[[PH_19]]،A18)،«Holiday»،«No»)، يمثّل النطاق $C$2:$D$11 تواريخ العطلات الرسمية لسنوات معيّنة، بينما تمثّل الخلية A18 التاريخ الذي تريد التحقّق مما إذا كان عطلة رسمية—ويمكنك تعديلها بحسب احتياجاتك. وستُرجع هذه الصيغة «عطلة» إذا كان التاريخ المحدّد ضمن العطلات الرسمية، و«لا» إذا لم يكن كذلك.
(2) يمكنك أيضًا تطبيق صيغة المصفوفة هذه=IF(OR($C$2:$D$11=A18),«Holiday»,«NO») للتحقق مما إذا كان التاريخ المقابل يقع في عطلة أم لا.
الجزء 3: حساب عدد الأيام بين تاريخين باستثناء عطلات نهاية الأسبوع والعطلات الرسمية في Excel
في الجزء 1، قمنا بإدراج جميع العطلات لسنة معيّنة، والآن ستُرشدك هذه الطريقة إلى حساب عدد الأيام مع استثناء عطلات نهاية الأسبوع والعطلات الرسمية ضمن نطاق التواريخ المحدَّد.
اختر خلية فارغة لعرض عدد الأيام، ثم أدخل الصيغة=NETWORKDAYS(E1,E2,B2:B10) فيها واضغط مفتاحEnter.
ملاحظة: في الخلايا أعلاه، تمثّل E1 تاريخ البدء لنطاق زمني معيّن، وتمثّل E2 تاريخ الانتهاء، بينما يشير B2:B10 إلى قائمة العطلات التي حسبناها في الجزء 1.
الآن ستحصل على عدد الأيام في نطاق تاريخ محدد، باستثناء عطلات نهاية الأسبوع والعطلات الرسمية.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل