آموزش تابع DATEDIF در اکسل
معرفی تابع DATEDIF
تابع DATEDIF یکی از کاربردیترین توابع اکسل برای محاسبه اختلاف بین دو تاریخ است. با این تابع میتوانید فاصله زمانی را به شکل «تعداد سال»، «تعداد ماه»، «تعداد روز» یا حتی «روزهای باقیمانده بعد از کسر سال/ماه» محاسبه کنید. این تابع برای محاسبه سن، سابقه کار، مدت قرارداد، مدت زمان تا سررسید، و گزارشهای منابع انسانی و مالی بسیار پرکاربرد است.
نکته مهم: DATEDIF در دسته توابع قدیمی اکسل است و ممکن است در لیست توابع (Insert Function) بهراحتی دیده نشود، اما در اکثر نسخههای اکسل کار میکند.
مثال ساده: فرض کنید تاریخ شروع در سلول A2 برابر 1402/01/15 و تاریخ پایان در سلول B2 برابر 1403/05/01 باشد. اگر بخواهید اختلاف را بر حسب «سال کامل» حساب کنید:
=DATEDIF(A2;B2;"Y")
خروجی این فرمول تعداد «سالهای کامل» بین دو تاریخ را برمیگرداند (یعنی سال ناقص حساب نمیشود).
کاربردهای اصلی تابع DATEDIF
- محاسبه سن (سال/ماه/روز) از تاریخ تولد تا امروز
- محاسبه سابقه کار کارمندان از تاریخ استخدام تا امروز
- محاسبه مدت زمان قراردادها (مثلاً چند ماه تا پایان قرارداد)
- محاسبه مدت زمان بین تاریخ سفارش و تاریخ تحویل
- محاسبه روزهای باقیمانده تا سررسید چک/قسط
- ساخت گزارشهای دورهای (مثلاً مشتریانی که بیش از 6 ماه غیرفعال بودهاند)
- محاسبه اختلاف تاریخها به صورت «ماه کامل» یا «روز باقیمانده» برای تحلیلهای دقیقتر
ساختار (Syntax)
=DATEDIF(start_date,end_date,unit)
=DATEDIF(تاریخ_شروع;تاریخ_پایان;"واحد")
آرگومانها
start_date (تاریخ شروع) / تاریخ مبنا برای شروع محاسبه
تاریخی که محاسبه اختلاف از آن شروع میشود. معمولاً تاریخ قدیمیتر است (مثل تاریخ تولد یا تاریخ شروع قرارداد). این مقدار میتواند یک تاریخ تایپشده، نتیجه یک فرمول، یا ارجاع به سلول باشد.
end_date (تاریخ پایان) / تاریخ مقصد برای پایان محاسبه
تاریخی که میخواهید اختلاف را تا آن حساب کنید (مثل امروز یا تاریخ پایان قرارداد). در اغلب موارد باید بزرگتر یا مساوی تاریخ شروع باشد؛ در غیر این صورت معمولاً خطا میگیرید.
unit (واحد محاسبه) / نوع خروجی
این آرگومان به صورت متن داخل کوتیشن نوشته میشود و تعیین میکند اختلاف چگونه محاسبه شود:
- “Y” تعداد سالهای کامل بین دو تاریخ
- “M” تعداد ماههای کامل بین دو تاریخ
- “D” تعداد روزهای کامل بین دو تاریخ
- “YM” تعداد ماههای باقیمانده بعد از کسر سالهای کامل
- “MD” تعداد روزهای باقیمانده بعد از کسر ماههای کامل (حساس و گاهی گیجکننده)
- “YD” تعداد روزهای باقیمانده بعد از کسر سالهای کامل
مثالهای ساده و پایه
1) محاسبه تعداد روز بین دو تاریخ
فرض کنید تاریخ شروع در A2 و تاریخ پایان در B2 است. برای اختلاف روز:
=DATEDIF(A2;B2;"D")
نتیجه: تعداد روزهای کامل بین دو تاریخ را برمیگرداند.
2) محاسبه تعداد ماه کامل
اگر بخواهید بدانید چند «ماه کامل» گذشته است:
=DATEDIF(A2;B2;"M")
نتیجه: ماه ناقص را حساب نمیکند.
3) محاسبه تعداد سال کامل (مثلاً سن)
اگر A2 تاریخ تولد و B2 تاریخ امروز (یا یک تاریخ مشخص) باشد:
=DATEDIF(A2;B2;"Y")
نتیجه: سن بر حسب سال کامل.
4) ساخت سن به شکل «سال و ماه»
برای اینکه سن را دقیقتر و قابل فهمتر نمایش دهید (سال کامل + ماه باقیمانده):
=DATEDIF(A2;B2;"Y")&" سال و "&DATEDIF(A2;B2;"YM")&" ماه"
نتیجه: یک متن مثل «23 سال و 4 ماه» تولید میشود.
مثالهای کاربردی و واقعی
1) محاسبه سابقه کار از تاریخ استخدام تا امروز
فرض کنید تاریخ استخدام در C2 است و میخواهید تا امروز (TODAY) سابقه را بگیرید:
=DATEDIF(C2;TODAY();"Y")
نتیجه: تعداد سالهای کامل سابقه کار.
2) تشخیص اینکه آیا قرارداد بیش از 12 ماه طول کشیده است (AND)
فرض کنید شروع قرارداد در A2 و پایان قرارداد در B2 است. میخواهید بررسی کنید آیا مدت قرارداد حداقل 12 ماه کامل بوده و پایان قرارداد هم از شروع قرارداد جلوتر است:
=AND(B2>=A2;DATEDIF(A2;B2;"M")>=12)
نتیجه: TRUE یا FALSE.
3) دستهبندی مشتریان بر اساس «ماههای عدم خرید» (IF + DATEDIF)
فرض کنید آخرین خرید مشتری در D2 ثبت شده و میخواهید وضعیت مشتری را بسنجید:
=IF(DATEDIF(D2;TODAY();"M")>=6;"ریسکی";"فعال")
نتیجه: اگر 6 ماه یا بیشتر خرید نکرده باشد «ریسکی» و در غیر این صورت «فعال».
4) شمارش تعداد سفارشهایی که دیرتر از 7 روز تحویل شدهاند (COUNTIF با ستون اختلاف)
روش استاندارد این است که ابتدا یک ستون کمکی بسازید. فرض کنید تاریخ سفارش در A2 و تاریخ تحویل در B2 است و در C2 اختلاف روز را محاسبه میکنید:
=DATEDIF(A2;B2;"D")
حالا برای شمارش سفارشهایی که اختلافشان بیشتر از 7 روز است (روی محدوده C2:C100):
=COUNTIF(C2:C100;">7")
نتیجه: تعداد مواردی که دیرتر از 7 روز تحویل شدهاند.
5) ترکیب XLOOKUP با DATEDIF برای محاسبه «مدت از آخرین رویداد»
فرض کنید کد پرسنلی در E2 است، در جدول رویدادها ستون A کد پرسنلی و ستون B تاریخ آخرین رویداد است. ابتدا تاریخ را با XLOOKUP میگیریم و سپس اختلاف تا امروز را حساب میکنیم:
=DATEDIF(XLOOKUP(E2;A:A;B:B);TODAY();"D")
نتیجه: تعداد روز از آخرین رویداد ثبتشده برای آن کد.
ترکیب تابع DATEDIF با فرمولهای دیگر
- DATEDIF + TODAY برای محاسبه سن/سابقه تا امروز
=DATEDIF(A2;TODAY();"Y")
- DATEDIF + IF برای وضعیتبندی (فعال/غیرفعال، مجاز/غیرمجاز)
=IF(DATEDIF(A2;B2;"D")>30;"دیرکرد";"بهموقع")
- DATEDIF + AND/OR برای شرطهای ترکیبی
=IF(AND(B2>=A2;DATEDIF(A2;B2;"M")<=3);"کوتاهمدت";"بلندمدت")
- DATEDIF + SUM (با ستون کمکی) برای جمع مدتها
=SUM(C2:C100)
- DATEDIF + XLOOKUP برای محاسبه مدت از یک تاریخ استخراجشده از جدول
=DATEDIF(XLOOKUP(E2;A:A;B:B);TODAY();"M")
خطاهای رایج و روش رفع آنها
1) خطای #NUM! به خاطر اینکه تاریخ پایان از تاریخ شروع کوچکتر است
اگر end_date (تاریخ پایان) قبل از start_date باشد، DATEDIF معمولاً #NUM! میدهد. راهحل: ترتیب تاریخها را اصلاح کنید یا در صورت نیاز تاریخها را با MIN و MAX مدیریت کنید.
=DATEDIF(MIN(A2;B2);MAX(A2;B2);"D")
2) خروجی اشتباه به خاطر اینکه تاریخها «متن» هستند نه تاریخ واقعی
گاهی تاریخها به شکل متن وارد شدهاند (بهخصوص پس از کپی از سیستمهای دیگر). در این حالت محاسبه درست نیست یا خطا میگیرید. راهحل: مطمئن شوید سلول واقعاً تاریخ است (Format Cells) یا تاریخ را به تاریخ قابل محاسبه تبدیل کنید.
3) استفاده اشتباه از unit (مثلاً نوشتن Y بدون کوتیشن)
آرگومان واحد باید حتماً داخل کوتیشن باشد و دقیقاً یکی از گزینههای معتبر باشد. مثلاً باید “Y” باشد نه Y.
=DATEDIF(A2;B2;"Y")
4) نتیجههای گیجکننده در “MD”
واحد “MD” (روزهای باقیمانده بعد از کسر ماهها) در برخی سناریوها میتواند خروجی غیرمنتظره بدهد، چون منطقش «روز باقیمانده در ماه» است، نه اختلاف روز ساده. اگر هدف شما اختلاف روز واقعی است از “D” استفاده کنید.
=DATEDIF(A2;B2;"D")
5) مشکل محاسبه به خاطر تقویم/زبان و سیستم تاریخ
اگر سیستم شما تاریخها را به شکل خاصی نمایش میدهد (مثلاً شمسی در بعضی افزونهها) اما اکسل تاریخ را به صورت عدد سریال میلادی ذخیره میکند، ممکن است ناسازگاری رخ دهد. راهحل: مطمئن شوید تاریخها «تاریخ قابل محاسبه اکسل» هستند و از یک روش یکسان برای ورود تاریخ استفاده میکنید.
نکات حرفهای و ترفندهای مهم
- برای سن دقیق، سال و ماه و روز را جداگانه بسازید تا خروجی قابل فهم شود.
=DATEDIF(A2;B2;"Y")&" سال، "&DATEDIF(A2;B2;"YM")&" ماه، "&DATEDIF(A2;B2;"MD")&" روز"
- قبل از محاسبه، از معتبر بودن تاریخها مطمئن شوید (بهخصوص دادههای واردشده از نرمافزارهای دیگر).
- برای جلوگیری از #NUM! ترتیب تاریخها را کنترل کنید (بهخصوص وقتی تاریخها ممکن است جابهجا وارد شوند).
=DATEDIF(MIN(A2;B2);MAX(A2;B2);"M")
- اگر فقط اختلاف روز میخواهید، سادهترین راه گاهی تفریق مستقیم است (چون تاریخ در اکسل عدد است).
=B2-A2
- خوانایی فرمول را با نامگذاری محدودهها بهتر کنید (مثلاً StartDate و EndDate) تا فرمولها قابل فهمتر شوند.
تفاوت تابع DATEDIF با توابع مشابه
- DATEDIF vs تفریق تاریخها (B2-A2)
تفریق تاریخها اختلاف را بر حسب «روز» میدهد و ساده است. اما DATEDIF میتواند «سال/ماه کامل» و حالتهای باقیمانده مثل “YM” را هم بدهد.
- DATEDIF vs YEARFRAC
YEARFRAC خروجی را به صورت «سال اعشاری» میدهد (مثلاً 2.5 سال). DATEDIF سال کامل میدهد (“Y”) یا ماه/روز کامل. برای محاسبههای مالی (بهره و…) YEARFRAC گاهی مناسبتر است.
- DATEDIF vs EDATE / EOMONTH
EDATE و EOMONTH برای جابهجایی تاریخ بر اساس ماهها هستند (مثلاً 3 ماه بعد). DATEDIF برای «محاسبه فاصله بین دو تاریخ» است.
- DATEDIF vs NETWORKDAYS
NETWORKDAYS اختلاف را بر اساس «روزهای کاری» (بدون تعطیلات آخر هفته و تعطیلات تعریفشده) محاسبه میکند. DATEDIF روزهای تقویمی را حساب میکند.
سازگاری با نسخههای مختلف اکسل
تابع DATEDIF در اکثر نسخههای اکسل (قدیمی تا جدید) قابل استفاده است، اما چون تابعی قدیمی و کمتر مستندسازیشده است، ممکن است در پنجره راهنمای توابع یا لیست توابع به شکل واضح نمایش داده نشود.
- Excel 2007، 2010، 2013، 2016، 2019، 2021 و Microsoft 365: پشتیبانی میشود
- در Excel for Web معمولاً قابل استفاده است، اما پیشنهاد میشود روی فایل واقعی هم تست شود
- در محیطهای محلیسازیشده، جداکننده آرگومانها ممکن است ; باشد (مثل بسیاری از سیستمهای فارسی)
سؤالات پرتکرار درباره تابع DATEDIF
آیا DATEDIF تاریخ شروع و پایان را شامل میشود؟
خیر، معمولاً اختلاف «بین» دو تاریخ را حساب میکند. اگر محاسبه شما نیاز به شامل کردن یک روز اضافه دارد، باید در منطق خودتان اصلاح کنید (مثلاً end_date را یک روز جلو ببرید).
چرا با “Y” سن من کمتر از چیزی میشود که انتظار دارم؟
چون “Y” فقط سالهای کامل را حساب میکند. اگر تولد شما هنوز در سال جاری نرسیده باشد، یک سال کمتر نمایش داده میشود که درست است.
بهترین واحد برای اختلاف دقیق روز چیست؟
“D” یا حتی تفریق مستقیم تاریخها (B2-A2) بهترین و شفافترین گزینهها هستند.
آیا میشود اختلاف را همزمان به سال و ماه و روز برگرداند؟
بله، با ترکیب چند DATEDIF و چسباندن متن میتوانید خروجی کامل بسازید.
جمعبندی و پیشنهاد یادگیری بعدی
تابع DATEDIF یک ابزار ساده و قدرتمند برای محاسبه اختلاف بین دو تاریخ است و با واحدهای مختلف مثل “Y” و “M” و “D” میتواند نیازهای رایج مثل سن، سابقه کار، مدت قرارداد و تحلیل تأخیرها را پوشش دهد. برای حرفهایتر شدن، پیشنهاد میشود بعد از DATEDIF سراغ توابع مرتبط با تاریخ و زمان بروید و اینها را یاد بگیرید: TODAY، EDATE، EOMONTH، YEARFRAC، NETWORKDAYS، و همچنین ترکیب شرطی با IF و AND/OR برای ساخت گزارشهای هوشمند.