آموزش محاسبه سابقه کار در اکسل با تابع DATEDIF

با تابع DATEDIF در اکسل می‌توانید سابقه کار هر پرسنل را از روز استخدام تا امروز، دقیق و به تفکیک سال، ماه و روز حساب کنید. چون تاریخ پایان را با تابع TODAY می‌دهید، سابقه هر روز خودبه‌خود به‌روز می‌شود و لازم نیست لیست را دستی اصلاح کنید.

سینتکس تابع DATEDIF

=DATEDIF(start_date, end_date, unit)

آرگومان سوم (unit) تعیین می‌کند خروجی بر حسب چه واحدی باشد:

واحد	خروجی
"y"	تعداد سال‌های کامل بین دو تاریخ
"m"	تعداد ماه‌های کامل بین دو تاریخ
"d"	تعداد کل روزهای بین دو تاریخ
"ym"	ماه‌های باقی‌مانده پس از کسر سال‌های کامل (سال و روز نادیده گرفته می‌شود)
"md"	روزهای باقی‌مانده پس از کسر سال و ماه کامل (سال و ماه نادیده گرفته می‌شود)
"yd"	روزهای باقی‌مانده پس از کسر سال‌های کامل

نکته: DATEDIF در فهرست توابع اکسل و در راهنمای تایپ نمایش داده نمی‌شود و باید نام آن را کامل تایپ کنید؛ این تابع برای سازگاری با Lotus 1-2-3 در اکسل نگه داشته شده اما در همه نسخه‌ها کار می‌کند. در نسخه‌های فارسی و منطقه‌ای اکسل، جداکننده آرگومان‌ها به‌جای ویرگول معمولا «نقطه‌ویرگول» ( ; ) است.

لیست نمونه پرسنل

لیست پرسنل در محدوده A1 تا F6 قرار دارد؛ ستون D تاریخ استخدام شمسی برای خواندن انسان و ستون E همان تاریخ به میلادی است که فرمول روی آن کار می‌کند:

فرمول محاسبه سابقه کار به سال و ماه و روز

1. در سلول F2 فرمول زیر را بنویسید و Enter بزنید:

=DATEDIF(E2,TODAY(),"y") & " سال " & DATEDIF(E2,TODAY(),"ym") & " ماه " & DATEDIF(E2,TODAY(),"md") & " روز"

این فرمول سه بار DATEDIF را روی یک بازه صدا می‌زند: "y" سال‌های کامل، "ym" ماه‌های باقی‌مانده و "md" روزهای باقی‌مانده را برمی‌گرداند و علامت & هر سه را با متن فارسی به هم می‌چسباند.

2. فرمول F2 را با درگ کردن دستگیره پر کردن تا انتهای لیست کپی کنید تا سابقه همه پرسنل حساب شود:

چون تاریخ پایان TODAY() است، این ستون فردا خودش یک روز جلو می‌رود؛ مقادیر بالا مربوط به تاریخ ۲۵ تیر ۱۴۰۵ است.

کاربرد سابقه کار در حقوق و دستمزد

همان خروجی DATEDIF ورودی مستقیم محاسبات حقوق و دستمزد است:

• پایه سنوات: به کارگر با حداقل یک سال سابقه تعلق می‌گیرد و بر مبنای سال‌های کامل سابقه حساب می‌شود. با مبلغ پایه سنوات روزانه (طبق بخشنامه مزد سال) در سلول $B$4: =DATEDIF(E2,TODAY(),"y")*30*$B$4
• عیدی و پاداش پایان سال: معادل دو ماه مزد و نسبت به روزهای کارکرد؛ =ROUND(L2*2*MIN(365,DATEDIF(E2,TODAY(),"d"))/365,0) که L2 حقوق پایه ماهانه است. سقف قانونی عیدی معادل ۹۰ روز حداقل مزد روزانه است.
• سنوات خدمت (پایان کار): برای هر سال سابقه یک ماه آخرین حقوق؛ با احتساب کسر سال به‌صورت نسبی: =ROUND(L2*DATEDIF(E2,TODAY(),"d")/365,0)
• مرخصی استحقاقی: مرخصی سالانه مشمولان قانون کار ۲۶ روز کاری است و نسبت به روزهای کارکرد تعلق می‌گیرد: =ROUND(26*MIN(365,DATEDIF(E2,TODAY(),"d"))/365,1)
• تعیین گروه شغلی و رتبه: ستون =DATEDIF(E2,TODAY(),"y") را می‌توانید مستقیم ورودی IF یا VLOOKUP جدول گروه‌بندی سابقه کنید.

نکته مهم درباره تاریخ شمسی

موتور تاریخ اکسل میلادی است. تاریخ شمسی که به‌شکل متن نوشته شده (مثلا ۱۳۹۵/۰۴/۱۵) از نظر اکسل «متن» است نه تاریخ، و DATEDIF روی آن خطای #VALUE! می‌دهد. راه عملی:

• یک ستون تاریخ استخدام میلادی معادل نگه دارید (ستون E در همین مثال) و فرمول را روی آن بنویسید.
• تعداد کل روزها (واحد "d") در هر دو تقویم دقیقا یکسان است، چون فاصله دو تاریخ به تقویم بستگی ندارد. پس برای محاسباتی که مبنای روز دارند (عیدی، سنوات و مرخصی) خروجی "d" بدون هیچ ملاحظه‌ای معتبر است.
• اما تفکیک سال و ماه و روز بر اساس مرز ماه‌های تقویم انجام می‌شود؛ بنابراین خروجی "y"/"ym"/"md" تفکیک میلادی است و ممکن است چند روز با تفکیک شمسی همان بازه فرق کند. اگر تفکیک دقیق شمسی لازم دارید، از افزونه‌های تاریخ شمسی اکسل استفاده کنید.

چند نکته کاربردی

• اگر تاریخ استخدام از تاریخ پایان بزرگ‌تر باشد، نتیجه #NUM! می‌شود. برای لیست‌هایی که ممکن است سلول خالی داشته باشند: =IF(E2="","",DATEDIF(E2,TODAY(),"y"))
• مایکروسافت هشدار می‌دهد واحد "md" در برخی حالت‌ها ممکن است عدد منفی یا نتیجه نادرست بدهد. اگر دقت روز برایتان بحرانی است، به‌جای آن روزهای باقی‌مانده را از ابتدای ماه تاریخ پایان حساب کنید یا فقط از "y" و "ym" و کل روز "d" استفاده کنید.
• برای محاسبه سابقه تا یک تاریخ مشخص (مثلا پایان سال مالی) به‌جای TODAY() آدرس همان سلول تاریخ را بگذارید: =DATEDIF(E2,$H$1,"y")
• برای سابقه فقط بر حسب ماه (مثلا شرط بیمه یا آزمایشی بودن): =DATEDIF(E2,TODAY(),"m")
• TODAY() تابعی فرار (volatile) است و با هر بار باز شدن فایل یا هر محاسبه دیگری دوباره حساب می‌شود؛ برای سابقه پرسنل شاغل دقیقا همین رفتار را می‌خواهیم.

📄 دانلود فایل اکسل نمونه «محاسبه سابقه کار با تابع DATEDIF» — شامل لیست نمونه پرسنل و همه فرمول‌های این آموزش به‌صورت زنده در شیت‌های جداگانه: سابقه کار به سال و ماه و روز، پایه سنوات، عیدی و سنوات پایان سال، مرخصی استحقاقی و راهنمای واحدهای DATEDIF.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting