فصل ۳: توابع کلیدی — منطقی، شرطی، متنی، تاریخ و گرد کردن

تاریخ و روزهای کاری: EDATE، EOMONTH، NETWORKDAYS.INTL با جمعه تعطیل و DATEDIF

تقویم کاری ایرانی در اکسل

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

توابع پایه

تابعکارمثال
TODAY()تاریخ امروز (فرار)برای «روزهای گذشته از سررسید»
EDATE(d, n)n ماه میلادی بعد یا قبلقسط ماهانه‌ی میلادی
EOMONTH(d, n)آخرین روز ماه میلادیEOMONTH(d,0)+1 = اول ماه بعد
WEEKDAY(d, 16)شماره‌ی روز هفته با شنبه = 1جمعه = 7
NETWORKDAYS.INTLتعداد روز کاری بین دو تاریخبا تعطیلی دلخواه
WORKDAY.INTLتاریخ پس از n روز کاریموعد تحویل سفارش

آخر هفته‌ی ایرانی

آرگومان weekend در توابع .INTL یا یک کد عددی است (16 = فقط جمعه، 7 = جمعه و شنبه) یا یک رشته‌ی هفت‌کاراکتری از دوشنبه تا یکشنبه که 1 یعنی تعطیل:

روز کاری با جمعه تعطیل و فهرست تعطیلات رسمی (نام Holidays):
=NETWORKDAYS.INTL(A2, B2, 16, Holidays)

پنج‌شنبه و جمعه تعطیل (دوشنبه … یکشنبه):
=NETWORKDAYS.INTL(A2, B2, "0001100", Holidays)

موعد تحویل: 12 روز کاری پس از ثبت سفارش
=WORKDAY.INTL(A2, 12, 16, Holidays)

روزهای تأخیر چک:
=MAX(0, TODAY()-C2)

سابقه‌ی کار به سال و ماه:
=DATEDIF(D2, TODAY(), "y") & " سال و " & DATEDIF(D2, TODAY(), "ym") & " ماه"

Holidays یک محدوده‌ی نام‌گذاری‌شده از تاریخ‌های (میلادی) تعطیلات رسمی سال است. آن را هر سال از تقویم رسمی تهیه کنید و تاریخ‌های شمسی را با جدول تقویم درس ۵ به تاریخ واقعی تبدیل کنید.

دام EDATE برای اقساط شمسی

EDATE ماه میلادی اضافه می‌کند. قسطی که ۱۵ فروردین شروع شده، با EDATE یک ماه بعد به ۱۴ یا ۱۵ اردیبهشت می‌افتد و به‌تدریج از روز ۱۵ ماه شمسی فاصله می‌گیرد. برای سررسیدهای شمسی دقیق، ماه شمسی را جلو ببرید و با جدول تقویم تاریخ واقعی را پیدا کنید:

دوازده سررسید ماهانه‌ی شمسی از تاریخ B1 (Excel 365):
=LET(s, TEXT(B1,"[$-fa-IR,16]yyyy/mm/dd"),
     y, --LEFT(s,4), m, --MID(s,6,2), d, RIGHT(s,2), k, SEQUENCE(12),
     ny, y+INT((m+k-1)/12), nm, MOD(m+k-1,12)+1,
     XLOOKUP(ny&"/"&TEXT(nm,"00")&"/"&d, Cal[Shamsi], Cal[Miladi], "روز ناموجود"))

نکته‌هایی که کمتر کسی می‌داند

  • DATEDIF در فهرست توابع و راهنمای خودکار اکسل نیست (از دوران Lotus باقی مانده) اما کار می‌کند؛ فقط حالت "md" آن در برخی تاریخ‌ها نتیجه‌ی منفی یا غلط می‌دهد و مایکروسافت هم استفاده‌اش را توصیه نمی‌کند.
  • WEEKDAY با آرگومان 16 شنبه را 1 می‌گیرد؛ پیش‌فرض (1) یکشنبه را 1 می‌داند و بسیاری از گزارش‌های «روز هفته» ایرانی به همین دلیل یک روز جابه‌جا هستند.
  • NETWORKDAYS هر دو تاریخ ابتدا و انتها را می‌شمارد؛ از شنبه تا همان شنبه «۱ روز کاری» است، نه صفر.
  • با رشته‌ی "0000000" هیچ روزی تعطیل نیست؛ برای کارخانه‌هایی که هفت روز هفته کار می‌کنند و فقط تعطیلات رسمی دارند، از همین استفاده کنید.
  • تفریق دو ساعت که از نیمه‌شب می‌گذرد (شیفت شب ۲۲ تا ۶) منفی می‌شود؛ =MOD(B2-A2,1) مدت درست را می‌دهد.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.