كيفية التحقق مما إذا كان التاريخ هو عطلة عامة وعد الأيام باستثناء أيام العطل في Excel؟
على سبيل المثال ، لديك قائمة بالتواريخ وترغب في التحقق مما إذا كانت هناك أي عطلات عامة موجودة في القائمة ، كيف يمكنك القيام بذلك بسرعة؟ سأوضح لك في هذه المقالة طرق حساب جميع تواريخ العطلات الرسمية الأمريكية في غضون سنوات معينة ، ثم تحقق مما إذا كان التاريخ هو عطلة رسمية أمريكية في Excel بسهولة.
- الجزء 1: حساب أيام العطل الرسمية خلال سنوات معينة في Excel
- الجزء 2: تحقق مما إذا كان التاريخ هو عطلة عامة في Excel
- الجزء 3: عد الأيام بين يومين باستثناء عطلات نهاية الأسبوع والعطلات في Excel
الجزء 1: حساب أيام العطل الرسمية لسنة معينة في Excel
قبل التحقق مما إذا كان التاريخ هو يوم عطلة عامة ، يجب عليك سرد جميع أيام العطل خلال سنوات معينة في Excel. لذلك فإن إعداد الجدول كما يلي لقطة الشاشة الموضحة سيجعل عملك أسهل.
هناك ثلاثة أنواع من العطلات الرسمية الأمريكية:
(1) النوع الأول هو يوم عطلة عامة في تاريخ محدد ، مثل يوم رأس السنة الجديدة في الأول من يناير. يمكننا بسهولة حساب يوم رأس السنة باستخدام الصيغة = DATE (السنة المعطاة ، 1,1،XNUMX);
(2) النوع الثاني هو يوم عطلة رسمية في يوم محدد من أيام الأسبوع ، مثل يوم الرئيس. يمكننا بسهولة حساب يوم الرئيس بالصيغة = التاريخ (السنة المعطاة ، 1,1،14) + 1,1 + اختر (WEEKDAY (DATE (السنة المحددة ، 1,0,6,5,4,3,2،XNUMX)) ، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX);
(3) والنوع الأخير هو يوم الذكرى ، يمكننا بسهولة حساب يوم الذكرى بالصيغة = DATE (السنة المعطاة ، 6,1،6,6) -WEEKDAY (التاريخ (السنة المعطاة ، XNUMX،XNUMX)).
هنا أسرد الصيغ لحساب جميع أيام العطل الرسمية في الجدول التالي. فقط أدخل الصيغ في الخلية المناسبة واضغط أدخل مفتاح واحد تلو الآخر.
| عطلات | الموبايل | الصيغ |
| يوم رأس السنة | C2 | = DATE (C1,1,1،XNUMX،XNUMX) |
| مارتن لوثر كينغ الابن يوم | C3 | = DATE (C1,1,1،14،1,1,1) + 1,0,6,5,4,3,2 + CHOOSE (WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX))، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX) |
| يوم الرئيس | C4 | = DATE (C1,2,1،14،1,2,1) + 1,0,6,5,4,3,2 + CHOOSE (WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX))، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX) |
| يوم الشهداء | C5 | = DATE (C1,6,1،1,6,6،XNUMX) -WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX)) |
| يوم الاستقلال | C6 | = DATE (C1,7,4،XNUMX،XNUMX) |
| عيد العمال | C7 | = DATE (C1,9,1،1,9,1،1,0,6,5,4,3,2) + اختر (WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX))، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX) |
| يوم كولومبوس | C8 | = DATE (C1,10,1،7،1,10,1) + 1,0,6,5,4,3,2 + CHOOSE (WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX))، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX) |
| يوم المحاربين القدامى | C9 | = DATE (C1,11,11،XNUMX،XNUMX) |
| عيد الشكر | C10 | = DATE (C1,11,1،21،1,11,1) + 4,3,2,1,0,6,5 + CHOOSE (WEEKDAY (DATE (CXNUMX،XNUMX،XNUMX))، XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX،XNUMX) |
| عيد الميلاد | C11 | = DATE (C1,12,25،XNUMX،XNUMX) |
ملحوظة: في الصيغ في الجدول أعلاه ، C1 هي الخلية المرجعية التي تحدد السنة المحددة. في مثالنا هذا يعني عام 2015 ، ويمكنك تغييره بناءً على احتياجاتك.
باستخدام هذه الصيغ ، يمكنك بسهولة حساب تواريخ أيام العطل الرسمية بالسنوات المحددة. انظر لقطة الشاشة أدناه:
احفظ نطاقًا كإدخال نص تلقائي (تنسيقات الخلايا المتبقية والصيغ) لإعادة استخدامها في المستقبل
يجب أن يكون إحالة الخلايا وتطبيق الصيغ أمرًا شاقًا جدًا لحساب كل عطلة. يوفر Kutools for Excel حلاً لطيفًا لـ النص السيارات أداة لحفظ النطاق كإدخال نص تلقائي ، والذي يمكن أن يظل تنسيقات الخلايا والصيغ في النطاق. وبعد ذلك ستعيد استخدام هذا النطاق بنقرة واحدة فقط. يصبح العمل سهلاً بمجرد نقرة واحدة لإدراج هذا الجدول وتغيير السنة في هذا الجدول!
كوتولس ل إكسيل - شحن برنامج Excel بأكثر من 300 أداة أساسية. استمتع بتجربة مجانية كاملة المواصفات لمدة 30 يومًا دون الحاجة إلى بطاقة ائتمان! احصل عليه الآن
الجزء 2: تحقق مما إذا كان التاريخ هو عطلة عامة في Excel
بعد إدراج جميع تواريخ العطلات الرسمية لسنوات معينة ، يمكننا بسهولة التحقق مما إذا كان التاريخ هو عطلة عامة أم لا باستخدام الصيغ في Excel. لنفترض أن لديك قائمة تواريخ كما هو موضح في لقطة الشاشة التالية ، وسأقدم لك طرقًا لإنجازها بسهولة.
حدد خلية فارغة بجانب قائمة التاريخ ، كما تقول الخلية B18 ، أدخل الصيغة = IF (COUNTIF ($ C $ 2: $ D $ 11، A18)، "Holiday"، "No") بداخله ، ثم اسحب مقبض التعبئة إلى النطاق الذي تريده. انظر لقطة الشاشة أعلاه:
ملاحظة:
(1) في الصيغة = IF (COUNTIF ($ C $ 2: $ D $ 11، A18)، "Holiday"، "No") ، فإن $ C $ 2: $ D $ 11 هو نطاق العطل الرسمية في سنوات محددة ، و A18 هي الخلية ذات التاريخ الذي تريد التحقق مما إذا كانت عطلة عامة ، ويمكنك تغييرها بناءً على احتياجاتك. وستقوم هذه الصيغة بإرجاع كلمة "عطلة" إذا كان التاريخ المحدد هو يوم عطلة رسمية ، وإرجاع "لا" إذا لم يكن كذلك.
(2) يمكنك أيضًا تطبيق صيغة الصفيف هذه = IF (OR ($ C $ 2: $ D $ 11 = A18)، "Holiday"، "NO") للتحقق مما إذا كان التاريخ المقابل هو عطلة أم لا.
الجزء 3: عد الأيام بين يومين باستثناء عطلات نهاية الأسبوع والعطلات في Excel
في الجزء الأول ، قمنا بإدراج جميع أيام العطل في سنة معينة ، والآن ستوجهك هذه الطريقة لحساب عدد الأيام باستثناء جميع عطلات نهاية الأسبوع والعطلات في نطاق زمني.
حدد خلية فارغة ستعيد عدد الأيام ، وأدخل الصيغة = NETWORKDAYS (E1، E2، B2: B10) فيه ، واضغط على أدخل الرئيسية.
ملاحظات: في الخلايا أعلاه ، E1 هو تاريخ بدء النطاق الزمني المحدد ، و E2 هو تاريخ الانتهاء ، و B2: B10 هي قائمة العطلات التي نحسبها في الجزء 1.
ستحصل الآن على عدد الأيام باستثناء عطلات نهاية الأسبوع والعطلات في النطاق الزمني المحدد.
انسخ الصيغ بشكل دقيق / ثابت دون تغيير مراجع الخلايا في Excel
كوتولس ل إكسيل نسخة طبق الأصل يمكن أن تساعدك الأداة المساعدة في نسخ صيغ متعددة بسهولة دون تغيير مراجع الخلايا في Excel ، مما يمنع تحديث مراجع الخلايا النسبية تلقائيًا.
كوتولس ل إكسيل - شحن برنامج Excel بأكثر من 300 أداة أساسية. استمتع بتجربة مجانية كاملة المواصفات لمدة 30 يومًا دون الحاجة إلى بطاقة ائتمان! احصل عليه الآن
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
