كيفية التعامل مع التواريخ والأوقات في برنامج Excel دون أخطاء

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

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

كيفية تفسير برنامج Excel للتواريخ والأوقات: المبدأ الأساسي

قبل أن ندخل في التفاصيل الدقيقة، نحتاج إلى فهم كيفية رؤية Excel للتواريخ والأوقات.. يقوم هذا البرنامج بتخزين التواريخ كأرقام تسلسلية متتاليةهذا يعني أن كل يوم يمر من تاريخ أساسي (افتراضيًا، ١ يناير ١٩٠٠، في معظم التثبيتات) يُقابله رقم. على سبيل المثال، الرقم ١ هو ١/١/١٩٠٠، والرقم ٢ هو ١/٢/١٩٠٠، وهكذا. من ناحية أخرى، يتم تمثيل الساعات على أنها جزء عشري من اليوم:وبالتالي فإن الظهر يساوي 0,5 (نصف يوم كامل)، والسادسة مساء يساوي 0,75، وهكذا.

لماذا هذا مهم؟ لأنه عند إدخال تاريخ مثل "25/12/2024"، يحفظ إكسل فعليًا عدد الأيام التي انقضت منذ عام 1900 ويعرضه كتاريخ بالتنسيق الذي تختاره. عند إدخال "12:00"، يظهر الرقم 0,5 في إكسل. هذه الثنائية بين المظهر والبيانات الفعلية هي ما يجعل العمليات الحسابية سهلة للغاية... وهي أيضًا سبب العديد من الأخطاء!

الإعدادات الإقليمية وتنسيقات التاريخ: الفخ الأول

هناك تفصيل واحد يجعل الأمور معقدة ويسبب معظم الأخطاء مع التواريخ: الإعدادات الإقليمية.بناءً على إعدادات جهاز الكمبيوتر لديك، قد يُفسّر Excel التاريخ على أنه يوم/شهر/سنة أو شهر/يوم/سنة. وبالتالي، قد يكون "08/12/2024" هو 8 ديسمبر أو 12 أغسطس. هذه الفوضى هي مصدر العديد من المشاكل.، خاصةً عندما تقوم بمشاركة الملفات مع مستخدمين آخرين أو استيراد البيانات من مصادر خارجية.

كيف يمكنني أن أعرف تنسيق التاريخ لديك؟ صحافة CTRL + 1 في أي خلية تحتوي على تاريخ، انتقل إلى الفئة تاريخ وانظر إلى التنسيقات التي تحمل علامة النجمة، وهي التنسيقات التي تعتمد على إعداداتك الإقليمية. من الضروري توحيد التنسيق والتحقق منه دائمًا قبل البدء في إجراء العمليات الحسابية.إذا كانت لديك أي أسئلة، استخدم وظيفة TEXT لعرض التاريخ بالتنسيق المطلوب (سنتناول هذا بمزيد من التفصيل لاحقًا).

العمليات الداخلية: الرقم التسلسلي وجزء اليوم

يتيح تخزين التواريخ كأرقام لبرنامج Excel إجراء عمليات الجمع والطرح وجميع أنواع العمليات بين التواريخ والأوقات.على سبيل المثال، لمعرفة عدد الأيام بين تاريخين، قم ببساطة بطرحهما: "=B1-A1" سيعطيك الفرق بالأيام.

في حالة الساعات، يعمل النظام عن طريق إضافة الكسر العشري المقابل إلى الرقم التسلسلي للتاريخ.وبالتالي، يُخزَّن "28/05/2024 10:12" كـ 45045,425. العدد الصحيح هو التاريخ، والجزء العشري هو الوقت. هذا يُتيح لك، على سبيل المثال، حساب عدد الساعات التي انقضت بين لحظتين محددتين بسهولة.

الإدخال الصحيح للتواريخ والأوقات: ما يمكنك وما لا يمكنك كتابته

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

• 1-1-2024 o 1/1/2024
• 01/01/24 o 01/01/2024
• 1 يناير 24 أو 1 يناير 2024
• 1 يناير 2024
• 1/1 (إذا قمت بحذف السنة، يقوم Excel بإضافتها افتراضيًا)

يجب عليك أن تكون أكثر صرامة مع الساعات.استخدم تنسيق H:MM أو H:MM:SS. يمكنك إضافة "صباحًا" أو "مساءً" إذا كان نظام التوقيت لديك ١٢ ساعة، ولكن عادةً ما يكفي نظام ٢٤ ساعة.

كن حذرا مع السنوات المكونة من رقمين: إذا أدخلت "01/01/09"، فهل تقصد 2009 أم 1909؟ في برنامج إكسل، السنوات التي تساوي أو تقل عن 29 هي سنوات القرن الحادي والعشرين، والسنوات من 30 إلى 99 هي سنوات القرن العشرين. لتجنب الالتباس، اكتب دائمًا السنوات بأربعة أرقام.

الفرق بين التاريخ والنص: مصدر الأخطاء

أحد الأخطاء الأكثر شيوعًا هو إدخال التواريخ كنص بدلاً من التواريخ الحقيقية.إذا كتبت "2024-05-22" ولم تتعرف الخلية عليه كتاريخ (تبقى محاذية لليسار وبدون تنسيق التاريخ)، فإن أي حساب سيعيد الخطأ المروع #يستحق! أو سلوكيات غريبة.

الحل: استخدم وظيفة DATE لفرض التحويل: =DATE(السنة؛ الشهر؛ اليوم)إذا كان لديك أجزاء التاريخ في خلايا مختلفة (A1: اليوم، B1: الشهر، C1: السنة)، استخدم =التاريخ(C1؛B1؛A1) لدمجهم في تاريخ واحد يتم التعرف عليه بشكل صحيح بواسطة Excel.

بالنسبة للأوقات، لا يمكن لبرنامج Excel تخزين الأوقات دون تاريخ مرتبط بها.إذا كتبت "17:30"، فسوف يفسرها برنامج Excel على أنها وقت اليوم صفر (0 يناير 1900)، ولكنها لا تزال تعمل لحسابات الوقت.

تخصيص التنسيق: كيفية عرض التواريخ والأوقات بالطريقة التي تريدها

بمجرد تخزين البيانات بشكل صحيح، يمكنك اختيار الطريقة التي تريد عرضها بها.. هذه مسألة تنسيق. اضغط CTRL + 1 للوصول إلى تنسيقات الخلايا. يمكنك الاختيار من بين تنسيقات محددة مسبقًا (قصير، طويل، ساعة، دقيقة، ثانية، إلخ) أو إنشاء تنسيق مخصص.

رموز التنسيق الشائعة للتاريخ والأوقات:

• dd - يوم مكون من رقمين (على سبيل المثال 05)
• DDDD - يوم كامل في الأسبوع (على سبيل المثال الأربعاء)
• mm - شهر مكون من رقمين (على سبيل المثال 07)
• mmmm - شهر كامل (على سبيل المثال يوليو)
• aa o AAAA - السنة مكونة من رقمين أو أربعة أرقام
• h: mm: ss - الساعة والدقيقة والثانية
• :mm:ss - إجمالي الساعات (للاختلافات التي تزيد عن 24 ساعة)

الخدعة: إذا كنت تريد عرض الشهر كنص، استخدم TEXT. على سبيل المثال ، =TEXT(A1؛ "مممم") يظهر الشهر كاملا.

الوظائف الرئيسية للتعامل مع التواريخ والأوقات

تتيح لك وظائف التاريخ والوقت في Excel القيام بكل شيء بدءًا من الحسابات البسيطة وحتى أتمتة المهام المتكررة.بعض من أكثرها فائدة:

دالة التاريخ

التاريخ(السنة؛ الشهر؛ اليوم) أنشئ تاريخًا من ثلاثة أرقام. هذا ضروري إذا كنت تُقسّم البيانات أو ترغب في التأكد من أن الخلية تُعرّف كتاريخ.

• العام: يمكن أن يتراوح من 1900 إلى 9999. إذا قمت بإدخال رقمين فقط، فسيعتمد ذلك على إعدادات Excel الخاصة بك.
• الشهر: إذا أدخلت أكثر من ١٢ شهرًا، يُضيف Excel الأشهر إلى السنة التالية. وبالمثل، إذا أدخلت أقل من ١، يُطرح منها الأشهر.
• اليوم: إذا تجاوزتَ عدد أيام الشهر، يُضيف Excel الفائض إلى الشهر التالي. إذا أدخلتَ رقمًا سالبًا، فسيتم إرجاع الأيام إلى الشهر السابق.

الأخطاء الشائعة: إذا كنت تستخدم سنة خارج النطاق أو أرقامًا سلبية مستحيلة، فسوف يعيد Excel القيمة #NUM!

وظيفة NOW

الآن() o الآن() يُظهر لك التاريخ والوقت الحاليين. إنه ديناميكي: يتغير مع كل إعادة حساب للورقة. مفيد للطوابع الزمنية التلقائية، وحسابات الوقت المنقضي، وما إلى ذلك.

إذا كنت تريد التاريخ الحالي فقط، استخدم اليوم() (اليوم() باللغة الإنجليزية).

وظيفة النص

النص(القيمة؛ تنسيق النص) يتيح لك تنسيق التاريخ أو الوقت كنص بالتنسيق الذي تريده بالضبط.

• على سبيل المثال: =TEXT(A1؛ "dd/mm/yyyy") يقوم بتحويل التاريخ الموجود في A1 إلى نص بهذا التنسيق.
• يمكنك استخدام "mmmm yyyy" لعرض "يوليو 2024" على سبيل المثال.
• للحصول على اسم اليوم أو الشهر: "dddd" أو "mmmm".

دالة الشهر

الشهر(التاريخ) يستخرج رقم الشهر من التاريخ. إذا كان A1 يحتوي على 20/05/2024، =الشهر(A1) يعود 5.

يمكنك أيضًا الاطلاع على المزيد من الميزات المفيدة في دليلنا حول صيغ Excel الأساسية.

دالة WORKDAYS (WORKDAYS)

يوم العمل(تاريخ البدء؛ أيام؛ ) يحسب أي يوم من أيام الأسبوع يقع بعد X أيام من تاريخ معين، متجاهلاً أيام السبت والأحد والعطلات الرسمية (إذا كانت موجودة في جدول).

• على سبيل المثال: =يوم العمل("01/09/2024"; 10) يعطي التاريخ بعد 10 أيام عمل.
• يمكنك طرح الأيام عن طريق إدخال قيم سلبية.

دالة YEARFRAC

YEARFRAC(تاريخ البدء؛ تاريخ الانتهاء؛ ) يُعطيك جزءًا من السنة بين تاريخين. مفيد جدًا لحساب الفوائد والقروض والأعمار، إلخ.

• قاعدة: يُحدد معيار حساب الأيام. القيمة الافتراضية هي 0 (US 30/360)، ولكن يُمكنك تحديد 1 (actual/actual)، أو 2 (actual/360)، أو 3 (actual/365)، أو 4 (european 30/360).

على سبيل المثال، بين 1/1/2023 و30/6/2023، =YEARFRAC(«01/01/2023»; «30/06/2023») يعطي 0,5 (نصف سنة).

إجراء حسابات بين التواريخ والأوقات في Excel

إضافة/طرح أيام إلى تاريخ

بإمكانك إضافة أو طرح أي عدد من الأيام إلى تاريخ ما ببساطة عن طريق عملية الجمع أو الطرح الأساسية.: إذا كان في A1 هو 10/04/2024، = A1 + 5 من 15/04/2024.

انتبه إلى تنسيق خلية النتيجة: إذا رأيت رقمًا غريبًا (على سبيل المثال 45000)، فما عليك سوى تنسيق الخلية كتاريخ، وسوف تنتهي من الأمر.

حساب الفرق بين تاريخين (بالأيام، الأشهر، السنوات)

لمعرفة الأيام بين تاريخين استخدم “=B1-A1”. إذا كنت تريد الفرق بالأشهر أو السنوات، يمكنك استخدام الوظائف:

• DATEDIF(تاريخ البدء؛ تاريخ الانتهاء؛ "م") – الفرق بالأشهر
• DATEDIF(تاريخ البدء؛ تاريخ الانتهاء؛ "y")) – الفرق في السنوات

ملاحظة: DATEDIF هي وظيفة "مخفية"؛ فهي لا تظهر في معالج Excel، ولكنها لا تزال تعمل.

إضافة/طرح الوقت إلى ساعة

إن إضافة الدقائق أو الساعات أو الثواني هي مسألة إضافة الكسر المناسب:إذا كان A1 هو "12:00"، لإضافة 90 دقيقة: = A1 + 90/1440.

لحساب مقدار الوقت الذي مر بين لحظتين: = (B1-A1) * 24 ويعطيك الإجمالي بالساعات (إذا كانت B1 وA1 عبارة عن تواريخ تحتوي على ساعات).

للحصول على الدقائق، اضرب في ١٤٤٠. إذا كانت النتائج سلبية، فقد تعرض الخلية "###". غيّر التنسيق إلى :mm:ss لرؤية الفروق السلبية.

التواريخ والأوقات المجمعة: العمل مع كليهما في نفس الوقت

عند العمل مع التواريخ والأوقات معًا، يقوم Excel بتخزين الجزء الصحيح كتاريخ والجزء العشري كوقت.بهذه الطريقة، يمكنك إجراء حسابات دقيقة بين نقاط زمنية محددة: على سبيل المثال، بين "15/04/2024 9:30 AM" و"16/04/2024 12:15 PM"، سيكون الفرق 1,11806 (يوم أو نحو ذلك)، والذي يمكنك تحويله إلى ساعات أو دقائق ببساطة عن طريق الضرب في 24 أو 1440.

الأخطاء الشائعة وكيفية تجنبها

تسبب الأخطاء عند العمل مع التواريخ والأوقات مشاكل أكثر من أي نوع آخر من البيانات في Excel.. فيما يلي المشكلات الأكثر شيوعًا وكيفية حلها:

1. ارتباك في تنسيق التاريخ

الكثير من الأخطاء تأتي من سوء تفسير التاريخ. (DD/MM/YYYY مقابل MM/DD/YYYY). الحل هو توحيد التنسيقات، وإذا لزم الأمر، فرض التنسيق باستخدام دالة TEXT.

2. تظهر خلية النتيجة ####

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

3. يظهر #¡VALUE!

يحدث هذا عندما تحاول إجراء عملية على خلية يعتبرها Excel نصًا وليس تاريخًا/وقتًا.الحل: إجبار Excel على التعرف على التاريخ باستخدام دالة DATE أو تحويل الخلايا إلى التنسيقات المناسبة.

4. خطأ #NUM!

يظهر هذا الخطأ عند محاولة إدخال تواريخ خارج النطاق المسموح به (قبل 1/1/1900 أو بعد 31/12/9999)إذا كنت بحاجة إلى تواريخ قديمة (تاريخية)، فقم بتخزينها كنص و/أو استخدم حيلًا إضافية.

5. مشاكل السنوات الكبيسة

يمكن للسنوات الكبيسة أن تؤدي إلى خلل في وظائف فرق التاريخ، وخاصة في الحسابات المالية.استخدم YEARFRAC بالتوافق المناسب لتجنب المفاجآت. لتحديد ما إذا كانت السنة كبيسة: =سنة كبيسة(سنة) o =MOD(السنة؛4)=0.

6. الغموض في السنوات المكونة من رقمين

لقد حدد برنامج Excel "سنة قطع" للسنوات المكونة من رقمين.قد يُسبب هذا أخطاءً تاريخية أو مالية. يُفضّل دائمًا إدخال السنوات بأربعة أرقام.

7. اختلافات المناطق الزمنية

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

نصائح عملية لعرض وتنسيق التواريخ والأوقات

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

• اختصارات سريعة: Ctrl+SHIFT+# تنسيق كتاريخ؛ Ctrl+SHIFT+@ مع مرور الوقت.
• أدخل التاريخ الحالي: Ctrl + ؛
• أدخل الوقت الحالي: Ctrl + Shift + ؛
• لعرض جزء فقط من التاريخ: استخدم التنسيق المخصص (على سبيل المثال، "mmmm yyyy" لعرض الشهر والسنة فقط باللغة الإسبانية).
• إذا أصبح التاريخ رقمًا "غريبًا": تنسيق الخلية كتاريخ أو استخدام الدالة TEXT.
• لإضافة الساعات/الدقائق بسهولة: استخدم تنسيقًا مخصصًا : مم لرؤية إجمالي الساعات، حتى لو كان مجموعها أكثر من 24 ساعة.

تغيير نظام التاريخ: وضع 1900 مقابل وضع 1904

يحتوي برنامج Excel على نظامين لحساب الأيام: وضع 1900 (المعتاد في Windows) ووضع 1904 (المستخدم في Mac)إذا جمعتَ ملفاتٍ تستخدم كلا النظامين، فقد تختلف التواريخ بمقدار ١٤٦٢ يومًا (أي حوالي أربع سنوات). يمكنك تغيير النظام من خلال الخيارات > خيارات متقدمة > عند حساب هذا المصنف > استخدام نظام التاريخ ١٩٠٤. لا أنصح بتغييره إلا إذا كان ضروريًا للغاية.

يتطلب التعامل مع التواريخ والأوقات في برنامج إكسل فهم أساسها الرقمي، واختيار التنسيق المناسب، والإلمام بالخصائص الإقليمية وحدود النظام. بالممارسة والاهتمام، ستتمكن من تجنب الأخطاء الشائعة وأتمتة الحسابات المعقدة، مما يوفر الوقت ويحسّن دقة تقاريرك.