Note: The other languages of the website are Google-translated. Back to English

كيفية التحقق مما إذا كان التاريخ هو عطلة عامة وعد الأيام باستثناء أيام العطل في Excel؟

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


الجزء 1: حساب أيام العطل الرسمية لسنة معينة في Excel

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

تحقق من المستندات إذا كانت عطلة عامة 1

هناك ثلاثة أنواع من العطلات الرسمية الأمريكية:

(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 ، ويمكنك تغييره بناءً على احتياجاتك.

باستخدام هذه الصيغ ، يمكنك بسهولة حساب تواريخ أيام العطل الرسمية بالسنوات المحددة. انظر لقطة الشاشة أدناه:

تحقق من المستندات إذا كانت عطلة عامة 2

احفظ نطاقًا كإدخال نص تلقائي (تنسيقات الخلايا المتبقية والصيغ) لإعادة استخدامها في المستقبل

يجب أن يكون إحالة الخلايا وتطبيق الصيغ أمرًا شاقًا جدًا لحساب كل عطلة. يوفر Kutools for Excel حلاً لطيفًا لـ النص السيارات أداة لحفظ النطاق كإدخال نص تلقائي ، والذي يمكن أن يظل تنسيقات الخلايا والصيغ في النطاق. وبعد ذلك ستعيد استخدام هذا النطاق بنقرة واحدة فقط. يصبح العمل سهلاً بمجرد نقرة واحدة لإدراج هذا الجدول وتغيير السنة في هذا الجدول!


ad auto الأمريكية العطل 1

كوتولس ل إكسيل - يتضمن أكثر من 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.

تحقق من المستندات إذا كانت عطلة عامة 6

ستحصل الآن على عدد الأيام باستثناء عطلات نهاية الأسبوع والعطلات في النطاق الزمني المحدد.

انسخ الصيغ بشكل دقيق / ثابت دون تغيير مراجع الخلايا في Excel

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


صيغ نسخة مطابقة للإعلان 3

كوتولس ل إكسيل - يتضمن أكثر من 300 أداة يدوية لبرنامج Excel. نسخة تجريبية مجانية كاملة الميزات 30اليوم ، لا حاجة لبطاقة ائتمان! احصل عليه الآن


أفضل أدوات إنتاجية المكتب

Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪

  • إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2021 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.
علامة تبويب kte 201905

يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
فرز التعليقات حسب
التعليقات (8)
لا يوجد تقييم. كن أول من يقيم!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
في محاولة لعمل صيغة لطرح أيام التقويم والأعياد. لقد تمكنت من معرفة أيام العمل والعطلات ، لكن لا يمكنني معرفة كيفية القيام بأيام التقويم والعطلات. هذا ما أستخدمه حاليًا في أيام العمل والعطلات. مساعدة! لذلك أحتاج إلى أن تكون أيام التقويم هذه بدلاً من أيام العمل.] = يوم العمل (B28-5,1،XNUMX ، HOLIDAYS)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كيف يمكنني جعل هذا يعمل في عطلة فيدرالية؟ بمعنى أنه إذا صادف تاريخ العطلة في عطلة نهاية الأسبوع ، فستكون العطلة الفيدرالية إما الجمعة أو الاثنين.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لقد استخدمت الصيغ أعلاه لحساب اليوم الفعلي للعطلة وعملت عمودًا ثانيًا للعطلة المرصودة. لقد صنعت هذه الصيغة لإنجاز هذا: = IF ((WEEKDAY (B15)) = 1، B15 + 1، IF ((WEEKDAY (B15)) = 7، B15-1، B15)). يشير مرجع الخلية B15 إلى العطلة الموجودة في عمود العطلة الفعلي ، في هذه الحالة يوم رأس السنة الجديدة. عندما تصادف العطلة الفعلية يوم السبت ، سيتم إدراج العطلة المرصودة على أنها يوم الجمعة ، وبالنسبة للعطلات الفعلية التي تقع يوم الأحد ، سيتم إدراج العطلة المرصودة على أنها يوم الإثنين. أتمنى أن يساعدك هذا.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هذه وظيفة دقيقة ستعمل في يوم رأس السنة الجديدة الذي سيصادف في عطلة نهاية الأسبوع (سنتا 2022 و 2023): = WORKDAY (DATE (CalendarYear ، 1,1،1,1) ، - (WEEKDAY (DATE (CalendarYear، 2،5) ، XNUMX)> XNUMX))
تم تصغير هذا التعليق بواسطة المشرف على الموقع
شكرًا ، لقد أعددت نظامًا لتحديد ما إذا كانت العطلة الرسمية هي أحد أيام الأسبوع ، ولكن هذا يوفر أيضًا بديلاً. المشكلة هي أنه إذا كان المرء يفعل ذلك شهريًا ، فهناك فجوات بين الأيام التي تحدث فيها أيام العطل الرسمية خلال أيام الأسبوع. على سبيل المثال أدناه جزء من ديسمبر 2017. الأرقام الموجودة على يمين التواريخ مباشرة (العمود B) هي قيم WEEKDAY. إذا كان التاريخ يقع في يوم السبت أو الأحد (القيمة 6 أو 7) ، فإن العمود C يعكس خلية فارغة ("") إذا كانت الخلية تحتوي على "1" في أحد أيام الأسبوع ، وإذا كانت هناك عطلة رسمية خلال أحد أيام الأسبوع ، فسيكون الرقم "0" 21 / 12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12 / 2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 يمكنني بعد ذلك الفرز يدويًا باستخدام نهج التصفية للحصول على 1 في عمود واحد مستمر من الصفوف بدون فراغات أو 0. انسخ والصق في ورقة عمل حيث يمكنني استيراد البيانات إلى مخططات درجة الحرارة. أحاول جعل قسم المرشح آليًا إما عن طريق الصيغة عن طريق حذف جميع 0 والخلايا الفارغة مع التحول الناتج للخلايا التي تحتوي على 1 ، أو عبر VBA. ستكون الجائزة النهائية هي الجمع بين الخطوات الموجودة في العمود A والعمود C في صيغة واحدة. تتمثل اللعبة النهائية في ملء مخطط درجة الحرارة باسم يوم العمل وفي الصف المقابل التالي يوم الشهر المطلوب الاثنين الثلاثاء الخميس الجمعة 7 8 10 11 باستخدام أغسطس كمثال حيث يكون اليوم التاسع هو عطلة عامة تقع أثناء العمل اليوم ، مما أدى إلى إزالة البيانات المتعلقة بيوم الأربعاء ونقل باقي العمود لأعلى مكان واحد (أو أكثر). ثم نقلت إلى الخلايا المذكورة أعلاه. آمل أن أشرح بوضوح كافٍ :-)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كيف أجعلها تظهر الاسم الفعلي للعطلة بدلاً من مجرد "عطلة"؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا KC ،
يمكنك تغيير النص "عطلة" إلى INDEX ($ A $ 1: $ A $ 11 ، MATCH (A18 ، $ C $ 1: $ C $ 11,0،XNUMX)) في الصيغة ، وسيتم تغيير الصيغة بالكامل إلى
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

يرجى ملاحظة أن التواريخ التي ستتحقق منها يجب أن توضع في عمود واحد.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كيف يمكنك حساب 15 يومًا تقويميًا من تاريخ معين بما في ذلك عطلات نهاية الأسبوع ولكن باستثناء أيام العطلات باستخدام قائمة / جدول تواريخ العطلات؟
لا توجد تعليقات منشورة هنا حتى الآن
اترك تعليقاتك
النشر كضيف
×
قيم المنشور:
0   الشخصيات
المواقع المقترحة

تواصل معنا

حقوق التأليف والنشر © 2009 - شبكة الاتصالات العالمية.extendoffice.com. | كل الحقوق محفوظة. مشغل بواسطة ExtendOffice. | | خريطة الموقع
Microsoft وشعار Office هما علامتان تجاريتان أو علامتان تجاريتان مسجلتان لشركة Microsoft Corporation في الولايات المتحدة و / أو دول أخرى.
محمي بواسطة Sectigo SSL