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

در اکسل تابع مشخصی برای محاسبه سن وجود ندارد. اما با استفاده از فرمول‌نویسی اکسل و چند تابع مختلف می‌توان با در دست داشتن تاریخ تولد، سن افراد را در شکل‌های مختلف سال، ماه، روز یا ترکیبی از آن‌ها تعیین کرد. در این مطلب از مجله فرادرس نحوه محاسبه سن در اکسل با تاریخ شمسی را از طریق پنج نوع فرمول‌نویسی با توابع YEARFRAC ،TODAY و DATEDIF همراه مثال بررسی می‌کنیم. نکته اصلی استفاده از این فرمول‌ها و توابع بر اساس تقویم شمسی، توجه به انجام تنظیمات مربوط در نرم‌افزار اکسل و ویندوز است. بنابراین در ابتدای بحث مروری بر این تنظیمات داریم تا هنگام انجام مثال‌ها با پیغام خطا مواجه نشویم.

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

محاسبه سن در اکسل با تاریخ‌های میلادی بدون نیاز به تنظیمات خاص قابل اجرا است. اما در تاریخ‌های شمسی قبل از شروع کار باید تنظیمات مربوط را در ویندوز و نرم‌افزار اکسل انجام دهیم. زیرا عدم توجه به هر یک از این موضوعات باعث نمایش اعداد منفی یا مشاهده عبارت #VALUE در محاسبه سن یا حتی نمایش تاریخ شمسی به شکل نادرست می‌شود. این تنظیمات در نسخه ۲۰۱۶ اکسل به بعد و ویندوز ۱۰ به شرح زیر هستند.

• تنظیم فرمت سلول از بخش «Format Cells» روی لوکیشن و تقویم «Persian»
• ایجاد قالب‌بندی سفارشی در سلول با کد [$-fa-IR,96]dd/mmmm/yyyy;@
• تغییر تنظیمات ویندوز  مربوط به تاریخ سیستم از بخش «Customize Format» در کنترل پنل و انتخاب تقویم هجری شمسی

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

همچنین در مطلبی با عنوان تاریخ شمسی در اکسل در مجله فرادرس، مراحل انجام این تنظیمات را به‌طور کامل بیان کرده‌ایم.

محاسبه سن با تابع TODAY

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

=(TODAY()-Cell address)/365

در این فرمول «cell address» نشانی همان سلولی است که می‌خواهیم محاسبات مربوط به سن را در مورد آن انجام دهیم.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

بنابراین با فرض درست بودن تنظیم تاریخ شمسی در نرم‌افزار اکسل، اگر تاریخ تولد افراد مختلفی را در جدول اکسل مانند تصویر زیر داشته باشیم، فرمول =(TODAY()-B2)/365 سن فرد مورد نظر در سلول B2 را محاسبه می‌کند. با کپی کردن فرمول در سایر ردیف‌های ستون B سن همه افراد به‌دست می‌آید.

این فرمول ساده است. با این حال همان‌طور که در نتایج می‌بینیم، تعداد رقم‌های اعشاری بسیاری در نتیجه حاصل وجود دارد. معمولا این نوع از نمایش اعداد برای سن یک فرد به‌کار نمی‌رود. بنابراین لازم است با استفاده از تابع INT نتیجه حاصل را به نزدیک‌ترین عدد صحیح به سمت پایین رند کنیم. بنابراین فرمول زیر را جایگزین می‌کنیم.

=INT((TODAY()-cell address)/365)

در تصویر زیر نتایج فرمول‌نویسی ترکیبی با این تابع برای سلول B2 نمایش داده شده است.

هر چند فرمول‌نویسی با تابع TODAY برای محاسبه سن در اکسل، ساده و دقیق است، با این حال نتایج آن در مورد سال‌های کبیسه دقیق نیست. زیرا در این حالت دیگر نمی‌توانیم عدد ۳۶۵ را در فرمول استفاده کنیم و سن افراد یک روز بزرگ‌تر محاسبه می‌شود. البته در مورد این سال‌های خاص می‌توانیم عدد ۳۶۶ را در فرمول درج کنیم، اما اگر بچه‌ای با سن کمتر از یک‌سال داشته باشیم، نتیجه محاسبه حتی با درج این عدد هم صحیح نخواهد بود. بنابراین استفاده از این روش ایده‌آل نیست و بهترین کار محاسبه سن از طریق توابع دیگر اکسل است.

محاسبه سن با تابع YEARFRAC

روش قابل اطمینان‌تر برای محاسبه سن در اکسل بر حسب تعداد سال، فرمول‌نویسی با تابع YEARFRAC است. برای این کار فرمول زیر را استفاده می‌کنیم.

=YEARFRAC(start_date, end_date, [basis])

در این فرمول تعریف هر یک از آرگومان‌ها عبارتند از:

• آرگومان «Start_date»: تاریخ تولد
• آرگومان «End_date»: تاریخ کنونی که توسط تابع TODAY مشخص می‌شود.
• آرگومان «Basis»: در این آرگومان اختیاری بر حسب استاندارد روز و ماه کشورهای مختلف اعداد ۰ تا ۴ با تعاریف زیر درج می‌شوند:
  • عدد ۰: محاسبه بر اساس سیستم آمریکایی با در نظر گرفتن ماه‌های ۳۰ روزه و سال ۳۶۰ روزه
  • عدد ۱: محاسبه بر اساس ماه و روز واقعی
  • عدد ۲: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۰ روزه
  • عدد ۳: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۵ روزه
  • عدد ۴: محاسبه بر اساس سیستم اروپایی با در نظر گرفتن ماه‌های ۳۰ روزه و سال ۳۶۰ روزه

برای درک بهتر عملیات، سن هر یک از افراد در مثال قبل را این‌بار از طریق فرمول =YEARFRAC(B2, TODAY(), 1) محاسبه می‌کنیم. در این فرمول برای نمایش نتیجه بر اساس ماه و روز واقعی، مقدار «Basis» را عدد ۱ در نظر می‌گیریم.

همان‌طور که مشخص است، بار دیگر، نتایج به شکل اعشاری هستند. بنابراین می‌توانیم علاوه بر تابع INT از تابع ROUNDDOWN نیز برای رند کردن اعداد به سمت پایین و با حذف تمامی اعشار آن‌ها به‌شکل زیر استفاده کنیم. در این فرمول اکسل به‌دلیل آن‌که نمی‌خواهیم هیچ عدد اعشاری در نتیجه نمایش داده نشود، مقدار صفر را برای آرگومان مربوط به تابع ROUNDDOWN در نظر می‌گیریم.

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)

یادگیری بهتر توابع و فرمول‌نویسی در فرادرس

یادگیری محاسبه سن در اکسل با تاریخ شمسی به دو مهارت انجام کامل تنظیمات اکسل بر اساس تقویم شمسی و دانش فرمول‌نویسی با توابع دارد. علاوه بر راهنمایی‌هایی که تا به اینجا برای هر یک از این موارد ارائه شد، پیشنهاد می‌کنیم برای تکمیل اطلاعات، فیلم‌های آموزشی فرادرس در این زمینه به شرح زیر را مشاهده کنید. زیرا در ادامه بحث به فرمول‌نویسی‌های پیچیده‌تری خواهیم پرداخت که نیاز به آشنایی بیشتری در کار با اکسل دارد.

• فیلم آموزش رایگان محاسبه روزهای بین دو تاریخ در فرادرس
• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل در فرادرس

همچنین دو مجموعه زیر تکمیل‌کننده مهارت‌های اصلی اکسل هستند که امکان دسترسی طبقه‌بندی شده آموزش‌ها را در یک قالب فراهم می‌کنند.

• مجموعه فیلم آموزش اکسل در فرادرس
• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس

محاسبه سن با تابع DATEDIF

روش سوم برای محاسبه سن در اکسل با تاریخ شمسی فرمول‌نویسی با تابع DATEIF به‌شکل زیر است.

=DATEDIF(start_date, end_date, unit)

در این فرمول سن بر اساس سال، ماه یا روز بر حسب فرمت درخواستی قابل نمایش است. آرگومان «Start_date» تاریخ تولد، آرگومان «end_date» تاریخ روز و آرگومان «unit» ترکیب مختلف خروجی مورد نظر بر حسب سال، ماه یا روز است. به‌عبارتی فرمول با توجه به عبارتی که در آرگومان «unit» تعریف می‌کنیم، خروجی‌های متفاوتی از سن را به شرح زیر نشان می‌دهد.

• خروجی «Y»: نمایش سن بر حسب تعداد کل سال
• خروجی «M»: نمایش سن بر حسب تعداد کل ماه‌ها
• خروجی «D»: نمایش سن بر حسب تعداد کل روزها
• خروجی «YM»: نمایش باقیمانده سن بر حسب ماه با حذف سال
• خروجی «MD»: نمایش باقیمانده سن بر حسب روز با حذف ماه
• خروجی «YD»: نمایش باقیمانده سن بر حسب روز با حذف سال

با توجه به اهمیت درک کامل توابع برای فرمول‌نویسی بدون خطا، پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول در اکسل در فرادرس را مشاهده کنید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

از‌آنجاکه ممکن است این تعاریف، نامفهوم به‌نظر برسند، مثال زیر با فرمول‌نویسی‌های مختلف برای آرگومان «unit» موضوع را روشن‌تر می‌کند.

مثال اول محاسبه سن بر حسب سال

در اولین مثال می‌خواهیم سن افراد را با در دست داشتن تاریخ تولد آن‌ها بر حسب سال حساب کنیم. بنابراین مقدار آرگومان «unit» برابر عبارت «Y» و فرمول به شکل =DATEDIF(B2,TODAY(),"Y") خواهد بود. لازم است توجه کنیم که عبارت «Y» را حتما در داخل گیومه (" ") بنویسیم. نتایج در تصویر زیر مشخص شده است.

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

مثال دوم محاسبه سن بر حسب ماه

برای محاسبه سن در اکسل با تاریخ شمسی بر حسب ماه از فرمول =DATEDIF(B2,TODAY(),"M") استفاده می‌کنیم. همان‌طور که مشخص است در این فرمول عبارت M در آرگومان «unit» قرار می‌گیرد. نتایج در مورد مثال قبل به شرح تصویر زیر است.

مثال سوم محاسبه سن بر حسب روز

در این حالت نیز کافی است در آرگومان «unit»، عبارت «D» را قرار دهیم. بنابراین فرمول مورد نظر به شکل =DATEDIF(B2,TODAY(),"D") خواهد بود. نتایج محاسبات به شرح تصویر زیر تعداد روزهای سپری شده از سن فرد را نشان می‌دهد.

مثال چهارم محاسبه سن بر حسب سال، ماه و روز

در بسیاری موارد برای واضح‌تر شدن نتایج محاسبات، لازم است مقدار عددی سن را به‌صورت ترکیبی از سال، ماه و روز نشان دهیم. در این حالت ترکیبی از فرمول‌نویسی با عبارات «YM»، «Y» و «MD» را در شکل فرمول‌نویسی‌های زیر استفاده می‌کنیم.

• فرمول‌نویسی برای محاسبه تعداد کل سال

=DATEDIF(B2, TODAY(), "Y")

• فرمول‌نویسی برای محاسبه باقیمانده سن بر حسب ماه

=DATEDIF(B2, TODAY(), "YM")

• فرمول‌نویسی برای محاسبه باقیمانده سن بر حسب روز

=DATEDIF(B2, TODAY(), "MD")

در هر حالت، فرمول به زبان ساده پاسخ این سوالات را می‌دهد.

• جایگزینی «Y»: «چند سال از سن فرد گذشته است؟»
• جایگزینی «YM»: «چند سال و چند ماه از سن فرد گذشته است؟». یعنی عملکرد به این شکل است که فرمول سن فرد بر حسب سال را کنار می‌گذارد و باقیمانده فاصله تولد تا تاریخ کنونی را به ماه محاسبه می‌کند.
• جایگزینی «MD»: «چند ماه و چند روز از سن فرد گذشته است؟» یعنی عملکرد به این شکل است که فرمول سن فرد بر حسب ماه را کنار می‌گذارد و باقیمانده فاصله تولد تا تاریخ کنونی را بر حسب روز حساب می‌کند.

ترکیب این سه فرمول نتیجه سن را بر حسب سال، ماه و روز نشان می‌دهد. بنابراین در مثال قبل فرمول اصلی به شکل زیر در می‌آید.

=DATEDIF(B2,TODAY(),"Y") & DATEDIF(B2,TODAY(),"YM") & DATEDIF(B2,TODAY(),"MD")

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

=DATEDIF(B2,TODAY(),"Y") & " سال و " & DATEDIF(B2,TODAY(),"YM") & " ماه و " & DATEDIF(B2,TODAY(),"MD") & " روز"

لازم است توجه کنیم برای نمایش صحیح عبارت از راست به چپ در سلول، بعد از کلیک سمت راست بر آن از مسیر Format cells> alignment بخش «Text Direction» را روی «Right-to-left» تنظیم می‌کنیم. نتایج فرمول‌نویسی در تصویر زیر مشخص است.

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

برای رفع این موضوع با استفاده از تابع IF یک شرط را به هر بخش از محاسبات فرمول اضافه می‌کنیم. به این شکل در صورت محاسبه صفر در عملیات، مقدار در نتیجه نمایش داده نمی‌شود. به‌عنوان مثال در بخش دوم فرمول، شرط IF(DATEDIF(B2, TODAY(),"ym")=0 در ابتدا اضافه شده است. بنابراین فرمول کامل به شکل زیر در خواهد آمد.

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

نتایج در جدول با حذف عدد صفر از ردیف C2 همانند تصویر زیر نشان داده می‌شود.

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

اگر بخواهیم پاسخی به این سوال دهیم که سن فرد در یک تاریخ مشخص چند سال، چند ماه و چند روز بوده است، این کار را با استفاده از تابع DATEDIF فرمول‌نویسی انجام می‌دهیم. تفاوت اصلی در اینجا جایگزینی تاریخ مورد نظر به‌جای «End_date» در فرمول اصلی است. به‌عبارتی، دیگر از تابع TODAY استفاده نمی‌کنیم.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

به‌عنوان مثال اگر بخواهیم سن افراد را در تاریخ ۱۳۹۹/۵/۱۰ محاسبه کنیم، فرمول به شکل زیر تغییر می‌کند.

=DATEDIF(B1, B2,"Y") & " سال و "& DATEDIF(B1,B2,"YM") & "ماه و "&DATEDIF(B1,B2, "MD") & "روز"

محاسبه تاریخ دقیق N سالگی فرد

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

=DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2))

همان‌طور که در تصویر مشخص است، تاریخی که فرد در آن ۵۰ ساله می‌شود (یا شده است) در ستون C قرار دارند. توجه داشته باشید درج عبارت $B$6 برای داینامیک کردن فرمول است. به این معنا که به‌راحتی با تغییر عدد مورد نظر در سلول B6 فرمول به‌صورت خودکار محاسبات را انجام می‌دهد و دیگر نیاز نداریم فرمول اصلی را تغییر دهیم.

جمع بندی پایانی

برای محاسبه سن در اکسل با تاریخ شمسی تابع مشخصی وجود ندارد. اما می‌توان با ترکیبی از توابع مختلف و فرمول‌نویسی‌های مربوط با توجه به تاریخ تولد سن را به دست آورد. در این مطلب از مجله فرادرس علاوه بر مرور کلی تنظیمات تاریخ شمسی در نرم‌افزار اکسل و ویندوز، پنج روش را برای انجام محاسبات سن همراه مثال تشریح کردیم.