محاسبه سن در اکسل و روزهای بین دو تاریخ

خیلی وقت ها پیش میاد که بخوایم مدت زمان گذشته از یک تاریخ شمسی رو حساب کنیم. پس در واقع میخوایم بدونیم سن یک چیز یا شخص چقدره. مثلا اینکه بخوایم سن یک نفر رو در اکسل حساب کنیم. یا اینکه مثلا مدت سابقه یک نفر که در یک شرکت مشغول بکار بوده رو حساب کنیم. برای آشنا شدن با نحوه محاسبه سن در اکسل کافیه مراحلی که در ادامه توضیح میدم رو قدم به قدم انجام بدین. در انتهای مطلب هم فایل اکسل این آموزش رو برای دانلود گذاشتیم.

تنظیمات لازم برای محاسبه سن با تاریخ شمسی

برای محاسبه سن در اکسل و کلا محاسبات زمانی باید اول با مفهوم تاریخ (به ویژه تاریخ شمسی) در اکسل آشنا باشیم. مفهوم تاریخ رو قبلا در مقالات متفاوتی بصورت کامل شرح دادیم. قبل از ادامه این بحث، مقاله مفهوم تاریخ در اکسل و مقاله تاریخ شمسی در اکسل رو حتما مطالعه کنید. اما در اینجا هم توضیحاتی رو ارائه میکنیم. برای اینکه بتونیم روی تاریخ (شمسی) محاسبات انجام بدیم باید اول چک کنیم ببینیم تاریخ ثبت شده از چه نوعی است؟ برای اینکه بتونیم به این سوال جواب بدیم باید اول سلول رو انتخاب کنیم، کلیک راست کرده و از قسمت Format Cells/ Number روی General قرار بدیم. وقتی این کار رو میکنیم ۲ حالت اتفاق میفته:

۱- به یک عدد ۵ رقمی تبدیل میشه

اگر با general کردن تبدیل به عدد ۵ رقمی شد، یعنی اینکه تاریخ قابل محاسبه است و خیلی راحت میتونیم مطابق با مثال زیر عملیات محاسبه سن در اکسل رو شروع کنیم. (توجه داشته باشید که تاریخ شمسی قابل محاسبه همونطور که در مقاله تاریخ توضیح داده شده، از ورژن ۲۰۱۶ به بعد در دسترس است).

۲- تغییری نمیکنه (به عدد ۵ رقمی تبدیل نمیشه)

اگر با general کردن تبدیل به عدد ۵ رقمی نشد؛ یعنی اینکه این تاریخ نیست بلکه یک عبارت متنی است و اکسل اصلا این رشته رو به عنوان تاریخ شناسایی نمیکنه. پس اول باید با استفاده از افزونه این تاریخ رو تبدیل به تاریخ شمسی قابل محاسبه کنیم و بعد مطابق مثال حل شده عمل کنیم. جهت دانلود و استفاده از افزونه تاریخ شمسی حتما مقاله تاریخ شمسی در اکسل رو مشاهده کنید.

حالا یک تاریخ شمسی قابل محاسبه داریم که میخوایم فاصله اونو تا یک تاریخ مشخص حساب کنیم.

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

حالا میخواهیم با یه مثال نحوه محاسبه سن در اکسل رو با هم انجام بدیم. فرض کنید که میخواهیم سن افراد رو تا پایان سال ۱۴۰۱ حساب کنیم. پس کافیه تاریخ تولد هر نفر رو از تاریخ مبدا مورد نظر (پایان سال ۱۴۰۱) کم کنیم. (شکل ۱) اما خب این فاصله به روز محاسبه میشه یعنی در نهایت به ما میگه سنمون چند روزه و چند روز از تاریخ تولد گذشته. اما ما میخوایم این فاصله رو به تفکیک سال، ماه و روز داشته باشیم. پس برای محاسبه سن در اکسل به این شکل، در ادامه با من همراه باشید.

محاسبه فاصله دو تاریخ (محاسبه اختلاف سن)

شکل ۱- محاسبه فاصله دو تاریخ

حالت های محاسبه فاصله بین دو تاریخ

در اکسل تابعی وجود داره به نام Datedif که فاصله دو تاریخ رو به حالت های مختلفی که نیاز داریم محاسبه میکنه و برای محاسبه سن در اکسل بسیار کاربردیه. این تابع از توابع مخفی اکسل هست که در لیست Functions نمیاد و آرگومان هاش در پیش نمایش دیده نمیشن. پس باید تعداد و نوع آرگومان هاش رو بشناسیم.

انواع آرگومان های تابع Datedif در اکسل

آرگومان اول:

Start Date یا تاریخ شروع (ابتدای بازه)

آرگومان دوم:

Finish Date یا تاریخ پایان (انتهای بازه)

آرگومان سوم:

نحوه نمایش این فاصله. (در ادامه با مثال شرح میدهیم)

آرگومان سوم	نحوه نمایش فاصله
“Y”	فاصله رو به سال نمایش میده
“M”	فاصله رو به ماه نمایش میده
“D”	فاصله رو به روز نمایش میده
“YM”	فاصله رو بصورت ماه باقیمانده بعد از سال نمایش میده
“MD”	فاصله رو بصورت روز باقیمانده بعد از ماه نمایش میده

نمایش فاصله دو تاریخ شمسی به سال، ماه و روز

تا اینجا نحوه محاسبه سن در اکسل رو یاد گرفتین. حالا میخوایم نتیجه رو به سال، ماه و روز بهمون برگردونه. مثلا فرض کنید، فاصله دو تاریخ که در ابتدای آموزش ارائه شد رو میخواهیم به سال ببینیم. کافیه تابع رو به شکل زیر ثبت کنیم و تا انتهای جدول درگ کنیم.

DATEDIF(B3,$A$1,”Y”)

شکل ۲- تابع Datedif و نمایش فاصله به سال

حالا میخواهیم ببینیم چند سال و چند ماه؟ مثلا برای نیکا، به ما بگه ۳۱ سال و چند ماه؟ پس میریم سراغ حالت YM. در این حالت، میاد ۳۱ سال رو میذاره کنار و باقیمانده فاصله رو به ماه حساب میکنه. یعنی:

DATEDIF(B3,$A$1,”YM”)

شکل ۳- محاسبه باقیمانده فاصله به ماه

حالا اگه بخوایم باقیمانده سال و ماه رو به روز هم بهمون بده، میریم سراغ حالت MD

DATEDIF(B3,$A$1,”MD”)

محاسبه باقیمانده فاصله دو تاریخ به روز (محاسبه اختلاف سن)

شکل ۴- محاسبه باقیمانده فاصله دو تاریخ به روز

نکته: ما تابعی داریم به نام Today که میتونه تاریخ روز جاری رو بهمون بده. پس میتونیم بجای سلول A1 از تابع Today استفاده کنیم که فاصله مورد نظر رو بصورت داینامیک تا روز جاری بهمون بده. پس با این کار تونستیم فاصله دو تاریخ رو به سال، ماه و روز محاسبه کنیم.

حالا کافیه که این اجزا رو به هم بچسبونیم و سن افراد رو به سال و ماه و روز در یک سلول ببینیم. اگر با روش های ساخت جمله داینامیک آشنا نیستید حتما مقاله مربوط به این موضوع رو مطالعه کنید.

C3&” سال و “&D3&” ماه و “&E3&” روز”

اگر تاریخ پایان رو از Today گرفته باشیم، هر بار که فایل رو باز کنیم، سن افراد تا روز جاری، بصورت داینامیک محاسبه میشه.تمام! حالا شما نحوه محاسبه سن در اکسل رو یاد گرفتین.