الصيغ مع التواريخ في التفوق. تعليمات لحساب الأيام. جمع وطرح الأشهر من التاريخ.

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

مثال 1

1. لمعرفة المدة التي قضاها الموظف في الشركة، عليك طرح تاريخ بدء العمل من التاريخ الحالي. إذا كانت ورقة العمل الخاصة بك تحتوي على تواريخ توظيف، فسيكون من الجيد إضافة تاريخ اليوم إلى ورقة العمل. (أسهل طريقة لإدخال التاريخ الحالي هي استخدام مجموعة المفاتيح CTRL SHIFT؛).

2. قم بتنشيط الخلية التي ستدخل إليها الأقدميةهذا الموظف.

3. أنت الآن بحاجة إلى طرح تاريخ التوظيف من التاريخ الحالي. في هذا المثال، يمكنك أولاً محاولة استخدام الصيغة =$B$2-C5. انتبه إلى النتيجة! اتضح مثل هذا رقم ضخملأن الصيغة تحسب عدد أيام (وليس سنوات) العمل في المؤسسة.

4. بقسمة النتائج على 365 نحصل على الإجابة بالسنوات. في حالتنا، يجب أن تبدو الصيغة =($B$2-C5)/365. من السهل الآن أن نرى أن الموظف الأول عمل في الشركة لأكثر من 22 عامًا.

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

إذا كنت قد اتخذت بالفعل دروسا في العمل مع التواريخ في Excel (لدينا أو أي دروس أخرى)، فيجب أن تعرف الصيغ لحساب وحدات الوقت، مثل الأيام والأسابيع والأشهر والسنوات.

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

كيفية طرح التواريخ في إكسل

لنفترض ذلك في خلاياك أ2و ب2يحتوي على تواريخ، وعليك طرح تاريخ من تاريخ آخر لمعرفة عدد الأيام بينهما. كما هو الحال غالبًا في برنامج Excel، يمكن الحصول على هذه النتيجة بعدة طرق.

مثال 1. اطرح تاريخًا من تاريخ آخر مباشرةً

أعتقد أنك تعلم أن برنامج Excel يخزن التواريخ كأعداد صحيحة تبدأ من 1، وهو ما يتوافق مع 1 يناير 1900. لذلك يمكنك ببساطة طرح رقم واحد من رقم آخر حسابيًا:

مثال 2: طرح التواريخ باستخدام الدالة DATEDAT

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

رازندات(A2;B2,"د")
=DATEDIF(A2,B2,"d")

يوضح الشكل التالي أن كلا الصيغتين ترجع نفس النتيجة، باستثناء الصف 4، حيث الدالة رازندات(DATEDIF) يُرجع خطأ #رقم!(#NUM!). دعونا نرى لماذا يحدث هذا.

عند طرح تاريخ لاحق (6 مايو 2015) من تاريخ سابق (1 مايو 2015)، تقوم عملية الطرح بإرجاع رقم سالب. ومع ذلك، بناء جملة الوظيفة رازندات(DATEDIF) لا يسمح تاريخ البدءكان أكثر تاريخ الانتهاءوبطبيعة الحال، إرجاع خطأ.

مثال 3. اطرح التاريخ من التاريخ الحالي

لطرح تاريخ محدد من التاريخ الحالي، يمكنك استخدام أي من الصيغ الموضحة مسبقًا. ما عليك سوى استخدام الوظيفة بدلاً من تاريخ اليوم اليوم(اليوم):

اليوم ()-A2
=اليوم()-A2

رازندات(A2;اليوم();"د")
=DATEDIF(A2,TODAY(),"d")

كما في المثال السابق، تعمل الصيغ بشكل جيد عندما يكون التاريخ الحالي أكبر من التاريخ المطروح. وإلا الوظيفة رازندات(DATEDIF) يُرجع خطأ.

المثال 4: طرح التواريخ باستخدام دالة التاريخ

إذا كنت تفضل إدخال التواريخ مباشرةً في الصيغة، فحددها باستخدام الوظيفة تاريخ(DATE) ثم قم بطرح تاريخ واحد من الآخر.

وظيفة تاريخلديه بناء الجملة التالي: تاريخ( سنة; شهر; يوم) .

على سبيل المثال، تطرح الصيغة التالية تاريخ 15 مايو 2015 من 20 مايو 2015 وترجع الفرق - 5 أيام.

التاريخ(2015,5,20)-التاريخ(2015,5,15)
=التاريخ(2015,5,20)-التاريخ(2015,5,15)

إذا لزم الأمر حساب عدد الأشهر أو السنوات بين تاريخين، ثم الدالة رازندات(DATEDIF) – الوحيد حل ممكن. ستجد في متابعة المقالة عدة أمثلة للصيغ التي تكشف عن هذه الوظيفة بالتفصيل.

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

كيفية إضافة (طرح) أيام إلى تاريخ في إكسيل

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

مثال 1: إضافة أيام إلى تاريخ في إكسيل

الصيغة العامة لإضافة عدد معين من الأيام إلى التاريخ هي:

= تاريخ + أيام N

يمكن تحديد التاريخ بعدة طرق:

مرجع الخلية:

استدعاء وظيفة تاريخ(تاريخ):

تاريخ(2015;5;6)+10
=التاريخ(2015,5,6)+10

استدعاء وظيفة أخرى. على سبيل المثال، لإضافة عدة أيام إلى التاريخ الحالي، استخدم الوظيفة اليوم(اليوم):

عمر الشخص هو الوقت الذي انقضى منذ ولادته. لذا كل ما علينا فعله هو وضع تاريخ اليوم في خلية وتاريخ ميلاد الشخص في خلية أخرى، ثم إزالة تاريخ ميلاده من اليوم وستحصل على عمرك - أليس كذلك؟ نظرًا لأنك أخذت التاريخ من الرقم التسلسلي من تاريخ آخر، فستحصل على عدد الأيام بينهما.

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

اليوم ()+10
=اليوم()+10

ويوضح الشكل التالي عمل هذه الصيغ. في وقت كتابة هذا التقرير، كان التاريخ الحالي هو 6 مايو 2015.

ملحوظة:نتيجة هذه الصيغ هي عدد صحيح يمثل التاريخ. لإظهاره كتاريخ، تحتاج إلى تحديد الخلية (أو الخلايا) والنقر عليها السيطرة+1. سوف يظهر صندوف حوار تنسيق الخلية(تنسيق الخلايا). على علامة التبويب رقم(رقم) في قائمة تنسيقات الأرقام، حدد تاريخ(التاريخ) ثم حدد التنسيق الذي تحتاجه. أكثر وصف تفصيليستجده في المقال.

مثال 2: طرح أيام من تاريخ في Excel

لطرح عدد معين من الأيام من تاريخ ما، تحتاج مرة أخرى إلى استخدام العملية الحسابية العادية. الفرق الوحيد عن المثال السابق هو ناقص بدلاً من علامة الجمع :-)

= تاريخ - أيام N

فيما يلي بعض الأمثلة على الصيغ:

أ2-10
=التاريخ(2015,5,6)-10
=اليوم()-10

كيفية إضافة (طرح) عدة أسابيع حتى تاريخ ما

عندما تحتاج إلى إضافة (طرح) عدة أسابيع ل تاريخ معينيمكنك استخدام نفس الصيغ كما كان من قبل. كل ما عليك فعله هو ضرب عدد الأسابيع في 7:

أضف N أسابيعحتى الآن في Excel:

A2+ ن أسابيع * 7

على سبيل المثال، لإضافة 3 أسابيع إلى تاريخ في خلية أ2، استخدم الصيغة التالية:

طرح N أسابيعمن التاريخ في Excel:

أ2 - ن أسابيع * 7

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

اليوم ()-2*7
=اليوم()-2*7

كيفية إضافة (طرح) عدة أشهر إلى تاريخ في إكسيل

لإضافة (أو طرح) عدد معين من الأشهر إلى تاريخ ما، تحتاج إلى استخدام الوظيفة تاريخ(التاريخ) أو البيانات(EDATE) كما هو موضح أدناه.

مثال 1: إضافة عدة أشهر إلى تاريخ باستخدام الدالة DATE

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

اكتب في الخلية ب2الصيغة أدناه، انقر على الزاوية المميزة للخلية واسحبها إلى أسفل العمود باستخدام الماوس بإلى آخر خلية مملوءة في العمود أ. صيغة من الخلية ب2سيتم نسخها إلى كافة خلايا العمود ب.

التاريخ(السنة(A2)، الشهر(A2)+$C$2، اليوم(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))

دعونا نرى ما تفعله هذه الصيغة. منطق الصيغة واضح وواضح. وظيفة تاريخ( سنة; شهر; يوم) يتلقى الحجج التالية:

• سنة من التاريخ في الخلية أ2;
• شهر من التاريخ في الخلية أ2+ عدد الأشهر المشار إليها في الخلية ج2;
• يوم من التاريخ في الخلية أ2;

انه سهل! إذا دخلت ج2الرقم السالب، ستقوم الصيغة بطرح الأشهر بدلاً من إضافتها.

وبطبيعة الحال، لا شيء يمنعك من إدخال الطرح مباشرة في صيغة طرح الأشهر:

التاريخ (السنة (A2)، الشهر (A2)-$C$2، اليوم (A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))

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

أضف أشهران يذهب في موعد:

التاريخ(السنة(A2)،الشهر(A2)+2،اليوم(A2))
=DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))

اطرح الأشهرمن التاريخ:

التاريخ(السنة(A2)،الشهر(A2)-2،اليوم(A2))
=DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))

المثال 2: إضافة أو طرح أشهر من تاريخ باستخدام الدالة DATAMES

يوفر برنامج Excel دالة خاصة تقوم بإرجاع تاريخ مضى عليه عدد معين من الأشهر أو أقدم من تاريخ معين - هذه هي الدالة البيانات(التاريخ). وهي متوفرة في أحدث الإصدارات Excel 2007 و2010 و2013 وExcel 2016 الجديد.

استخدام البيانات(EDATE) يمكنك تقديم الوسيطتين التاليتين:

• تاريخ البدء - التاريخ الذي يتم من خلاله حساب عدد الأشهر.
• شهور – عدد الأشهر المراد إضافتها (رقم موجب) أو طرحها (رقم سالب).

ستعطي هذه الصيغ نفس نتيجة الصيغ ذات الدالة تاريخ(DATE) في المثال السابق:

عند استخدام الوظيفة البيانات(EDATE) يمكن تحديد تاريخ البدء وعدد الأشهر مباشرة في الصيغة. يمكن ضبط التواريخ باستخدام الوظيفة تاريخ(التاريخ) أو نتيجة لتنفيذ صيغ أخرى. على سبيل المثال:

تضيف هذه الصيغة 10 أشهر إلى 7 مايو 2015

البيانات (التاريخ(2015،5،7)،10)
=EDATE(DATE(2015,5,7),10)

تطرح هذه الصيغة 10 أشهر من تاريخ اليوم

البيانات (اليوم ()؛ -10)
=EDATE(TODAY(),-10)

ملحوظة:وظيفة البيانات(EDATE) يُرجع عددًا صحيحًا فقط. لتمثيلها كتاريخ، تحتاج إلى تطبيق تنسيق تاريخ على الخلية. كيفية القيام بذلك موضحة في المقالة كيفية تغيير تنسيق التاريخ في إكسيل.

كيفية إضافة (طرح) سنوات إلى تاريخ في إكسيل

إن إضافة السنوات إلى التواريخ في Excel هو نفس إضافة الأشهر. تحتاج إلى استخدام الوظيفة مرة أخرى تاريخ(DATE)، ولكن هذه المرة تحتاج إلى تحديد عدد السنوات التي تريد إضافتها:

التاريخ (السنة) تاريخ) + ن سنوات; شهر( تاريخ); يوم( تاريخ))
= التاريخ(السنة( تاريخ) + ن سنوات،شهر( تاريخ)، يوم( تاريخ))

في ورقة عمل Excel، قد تبدو الصيغ كما يلي:

أضف 5 سنواتإلى التاريخ المحدد في الخلية أ2:

التاريخ(السنة(A2)+5،الشهر(A2)،اليوم(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))

اطرح 5 سنواتمن التاريخ المحدد في الخلية أ2:

التاريخ(السنة(A2)-5،الشهر(A2)،اليوم(A2))
=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))

للحصول على صيغة عالمية، يمكنك إدخال عدد السنوات في خلية ما، ثم الرجوع إلى تلك الخلية في الصيغة. سيضيف الرقم الموجب سنوات إلى التاريخ، وسيتم طرح الرقم السالب.

إضافة (طرح) الأيام والأشهر والسنوات إلى تاريخ

إذا قرأت المثالين السابقين بعناية، فأعتقد أنك عرفت كيفية إضافة (أو طرح) السنوات والأشهر والأيام إلى تاريخ في صيغة واحدة. نعم، باستخدام الوظيفة القديمة الجيدة تاريخ(بيانات)!

ل الإضافاتسنوات X وأشهر Y وأيام Z:

التاريخ (السنة) تاريخ) + سنوات X; شهر( تاريخ) + ي أشهر; يوم( تاريخ) + أيام Z)
= التاريخ(السنة( تاريخ) + سنوات X،شهر( تاريخ) + ي أشهر، يوم( تاريخ) + أيام Z)

ل الطرحسنوات X وأشهر Y وأيام Z:

التاريخ (السنة) تاريخ) - سنوات X; شهر( تاريخ) - ي أشهر; يوم( تاريخ) - أيام Z)
= التاريخ(السنة( تاريخ) - سنوات X،شهر( تاريخ) - ي أشهر، يوم( تاريخ) - أيام Z)

على سبيل المثال، تقوم الصيغة التالية بإضافة سنتين و3 أشهر، وطرح 15 يومًا من التاريخ الموجود في الخلية أ2:

التاريخ(السنة(A2)+2;الشهر(A2)+3;اليوم(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)

عند تطبيقها على عمود التاريخ، تأخذ الصيغة النموذج التالي:

DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)

كيفية إضافة وطرح الوقت في إكسل

في مايكروسوفت اكسليمكنك إضافة وطرح الوقت باستخدام الوظيفة وقت(وقت). يتيح لك التعامل مع وحدات الوقت (الساعات والدقائق والثواني) بنفس طريقة السنوات والأشهر والأيام في الوظيفة تاريخ(تاريخ).

إضافة وقتفي إكسل:

A2 + الوقت( يشاهد; دقائق; ثواني)
= A2 + الوقت( يشاهد, دقائق, ثواني)

اطرح الوقتفي إكسل:

أ2 - الوقت( يشاهد; دقائق; ثواني)
= A2 - الوقت( يشاهد, دقائق, ثواني)

أين أ2– هذه خلية بها الوقت الذي يجب تغييره.

على سبيل المثال، لإضافة ساعتين و30 دقيقة و15 ثانية إلى الوقت في الخلية أ2تحتاج إلى استخدام الصيغة التالية:

A2+الوقت(2;30;15)
=A2+الوقت(2,30,15)