شناسایی و رنگی کردن ماه تولد پرسنل در اکسل با تابع MONTH و فرمت شرطی

در واحد حقوق و دستمزد بسیاری از بیمارستان ها، هر ماه باید فهرست پرسنلی که تولدشان در همان ماه است برای پرداخت هدیه تولد یا ارسال پیام تبریک استخراج شود. اگر گزارش پرسنلی چند صد ردیف داشته باشد، پیدا کردن دستی این افراد از روی ستون تاریخ تولد وقت گیر و پر از اشتباه است. با ترکیب تابع MONTH و فرمت شرطی (Conditional Formatting)، اکسل به طور خودکار و همیشه به روز، ردیف افرادی که ماه تولدشان با ماه جاری یکی است را رنگی می کند؛ بدون هیچ فرمول کمکی در ستون کناری.

داده نمونه؛ گزارش پرسنل با تاریخ تولد

فرض کنید جدول زیر را داریم؛ فهرست پرسنل بیمارستان با کد پرسنلی، بخش محل خدمت و تاریخ تولد. تاریخ تولد به صورت یک تاریخ واقعی اکسل (نه متن ساده) وارد شده تا توابع تاریخ مثل MONTH روی آن درست کار کنند:

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

فرمول فرمت شرطی برای رنگی کردن ماه تولد جاری

تابع MONTH از یک تاریخ فقط شماره ماه (عددی بین ۱ تا ۱۲) را برمی گرداند؛ فارغ از روز و سال. وقتی این تابع را روی TODAY() اجرا کنیم، MONTH(TODAY()) شماره ماه همین امروز را می دهد؛ عددی که هر بار فایل باز شود، خودش را با تاریخ واقعی روز به روز می کند. با مقایسه ماه تاریخ تولد هر ردیف با این عدد، می توان مستقیم در فرمت شرطی از آن استفاده کرد.

مراحل ساخت قانون فرمت شرطی:

1. محدوده جدول (مثلا B2:E11) را انتخاب کنید.
2. از تب Home وارد Conditional Formatting شوید و گزینه New Rule را بزنید.
3. نوع قانون Use a formula to determine which cells to format را انتخاب کنید.
4. در کادر فرمول بنویسید: =MONTH($E2)=MONTH(TODAY()) (فرض بر این است که ستون تاریخ تولد، ستون E است).
5. علامت $ فقط قبل از حرف ستون (E) گذاشته شده تا هنگام اعمال روی چند ستون کنار هم، هر ردیف با تاریخ تولد همان ردیف مقایسه شود، نه ستون دیگری.
6. روی دکمه Format رنگ پرکردن دلخواه (مثلا سبز روشن) را انتخاب و OK را بزنید.

از این پس، هر ماه که گزارش را باز کنید، ردیف های پرسنلی که تولدشان همان ماه است بدون هیچ تغییری در فرمول یا فیلترکردن دستی، به رنگ انتخابی مشخص می شوند.

چرا فرمت شرطی و نه یک ستون کمکی TRUE FALSE؟

• می شد در یک ستون کنار جدول فرمول =MONTH(E2)=MONTH(TODAY()) نوشت و نتیجه TRUE یا FALSE را دید؛ اما این ستون اضافه، گزارش را شلوغ می کند و باید هر بار فیلتر یا مرتب شود.
• فرمت شرطی همان منطق را مستقیم روی ظاهر جدول اعمال می کند؛ کاربر با یک نگاه به رنگ ردیف ها متوجه می شود چه کسانی تولدشان این ماه است، بدون نیاز به خواندن مقدار TRUE FALSE.
• فرمت شرطی هنگام چاپ گزارش یا ارسال آن به عنوان PDF هم حفظ می شود؛ در حالی که یک ستون کمکی معمولا قبل از چاپ باید پنهان یا حذف شود.

تکنیک پیشرفته؛ هشدار تولدهای هفته آینده

برای اینکه واحد حقوق و دستمزد از قبل برای خرید هدیه یا هماهنگی تولد آماده شود، می توان یک قدم جلوتر رفت و به جای «ماه جاری»، «۷ روز آینده» را بررسی کرد. چالش اینجاست که تاریخ تولد شامل سال تولد واقعی فرد است، نه سال جاری؛ پس ابتدا باید با تابع DATE، «تولد امسال» فرد را با همان روز و ماه تولد ولی سال جاری بسازیم:

فرمول ساخت تولد امسال: =DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)). سپس با یک فرمول دوم بررسی می کنیم این تاریخ ساخته شده، بین امروز و ۷ روز بعد قرار دارد یا نه: =AND(F2>=TODAY(),F2<=TODAY()+7) (که F ستون تولد امسال محاسبه شده در فرمول قبلی است). همین فرمول دوم مستقیم در New Rule فرمت شرطی هم قابل استفاده است تا ردیف های نزدیک به تولد، جدا رنگی شوند.

نکات کاربردی

• این روش فقط وقتی درست کار می کند که ستون تاریخ تولد یک تاریخ واقعی اکسل باشد؛ اگر تاریخ به صورت متن ساده و به شکل شمسی وارد شده باشد (مثلا ۱۳۷۰/۰۴/۱۸ به عنوان متن، نه تاریخ)، تابع MONTH خطا می دهد یا نتیجه اشتباه برمی گرداند. در این حالت باید شماره ماه را با تابع متنی MID استخراج کرد؛ مثلا =VALUE(MID(E2,6,2)) به جای MONTH(E2).
• اگر بیمارستان از تقویم شمسی برای نمایش تاریخ ها استفاده می کند (از طریق Calendar Type فارسی در Format Cells)، توجه کنید که MONTH و TODAY همچنان بر اساس عدد سریال میلادی زیرین محاسبه می شوند، نه ماه نمایشی شمسی؛ برای تطبیق کامل با تقویم شمسی معمولا به یک ستون کمکی تبدیل تاریخ یا افزونه جداگانه نیاز است.
• همین تکنیک برای یادآوری سالگرد استخدام (برای محاسبه سنوات یا پاداش سالانه) هم قابل استفاده است؛ کافی است ستون تاریخ تولد را با تاریخ استخدام جایگزین کنید.
• اگر جدول پرسنلی هر ماه ردیف جدید می گیرد، محدوده را به صورت جدول اکسل (Insert > Table) تعریف کنید تا قانون فرمت شرطی روی ردیف های جدید هم خودکار اعمال شود.

📄 دانلود فایل اکسل نمونه «رنگی کردن ماه تولد پرسنل با فرمت شرطی» — شامل گزارش پرسنل با قانون فرمت شرطی زنده بر پایه MONTH و TODAY، و یک برگه جداگانه با فرمول پیشرفته هشدار تولدهای ۷ روز آینده، برای تمرین گام به گام.

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