كيفية التحقق مما إذا كان التاريخ هو عطلة عامة وعد الأيام باستثناء أيام العطل في 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 حلاً لطيفًا لـ النص السيارات أداة لحفظ النطاق كإدخال نص تلقائي ، والذي يمكن أن يظل تنسيقات الخلايا والصيغ في النطاق. وبعد ذلك ستعيد استخدام هذا النطاق بنقرة واحدة فقط. يصبح العمل سهلاً بمجرد نقرة واحدة لإدراج هذا الجدول وتغيير السنة في هذا الجدول!
كوتولس ل إكسيل - يتضمن أكثر من 300 أداة يدوية لبرنامج Excel. نسخة تجريبية مجانية كاملة الميزات 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 ، مما يمنع تحديث مراجع الخلايا النسبية تلقائيًا.
كوتولس ل إكسيل - يتضمن أكثر من 300 أداة يدوية لبرنامج Excel. نسخة تجريبية مجانية كاملة الميزات 30اليوم ، لا حاجة لبطاقة ائتمان! احصل عليه الآن
أفضل أدوات إنتاجية المكتب
Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪
- إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
- سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
- دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات
- حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
- نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
- استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
- سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
- اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
- أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2021 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
