اولگا کتاب ها را برای یک مدرسه خصوصی نگه می دارد. برای هر دانش آموز ثبت نام شده، او یک تاریخ ورود و یک تاریخ ترک دارد. او باید تعیین کند که برای هر دانش آموز چند ماه صورتحساب بگیرد. اگر دانش آموز حداقل پنج روز در کلاس بود، آن ماه باید در صورتحساب لحاظ شود. اگر کمتر از پنج روز باشد، برای آن ماه صورتحساب دریافت نمی شود. او همچنین باید تعطیلات و تعطیلات آخر هفته را حذف کند.
یکپارچه برای هر راه حلی برای این مشکل استفاده از تابع NETWORKDAYS است. این تابع، همانطور که در ExcelTips دیگر توضیح داده شده است، تعداد خالص روزهای کاری بین دو تاریخ را محاسبه می کند. آخر هفته ها و در صورت تمایل، تعطیلات را در نظر می گیرد.
بنابراین، با فرض اینکه تاریخ ورود دانش‌آموزان را در A1 و تاریخ خروج را در A2 دارید، تنها کاری که باید انجام دهید این است که لیستی از تعطیلات را تنظیم کنید. شما می توانید این کار را به سادگی با شروع به ورود به تعطیلات در محدوده ای از سلول ها انجام دهید. یک تاریخ در هر سلول وارد کنید و سپس محدوده را انتخاب کنید. یک نام برای اشاره به محدوده تعریف کنید، مانند MyHolidays.
سپس می توانید از فرمولی مانند زیر استفاده کنید:
=(NETWORKDAYS(A1, DATE(YEAR(A1), MONTH(A1) + 1,0), MyHolidays) >= 5)
+ (NETWORKDAYS(DATE(YEAR(A2), MONTH(A2),1), A2, MyHolidays) >= 5)
+ DATEDIF(DATE(YEAR(A1), MONTH(A1) + 1, 1), DATE(YEAR(A2),
MONTH(A2), 1), "m")
فرمول بسیار طولانی است و باید بررسی شود. توجه داشته باشید که علاوه بر تابع NETWORKDAYS، از تابع DATEDIF نیز استفاده می کند که برای تعیین تفاوت بین دو تاریخ و برگرداندن فاصله به روش های مختلف استفاده می شود. در این مثال، از آن با پارامتر "m" استفاده می شود، به این معنی که بازه زمانی را به صورت چند ماه برمی گرداند - دقیقاً همان چیزی که اولگا نیاز دارد.
قسمت اول فرمول (اولین استفاده از تابع NETWORKDAYS) برای تعیین چند روز بین تاریخ ورود (در سلول A1) و پایان ماهی که تاریخ ورود در آن رخ می دهد استفاده می شود. اگر این مقدار بزرگتر یا مساوی 5 (قطع اولگاس) باشد، مقدار 1 برگردانده می شود، زیرا این به عنوان یک ماه قابل پرداخت به حساب می آید.
قسمت بعدی فرمول (دومین استفاده از تابع NETWORKDAYS) برای تعیین اینکه آیا حداقل پنج روز کلاس در ماهی وجود دارد که تاریخ حذف در آن رخ می دهد یا خیر استفاده می شود. اگر چنین است، مقدار 1 بازگردانده می شود، زیرا این یک ماه قابل پرداخت است.
سپس تابع DATEDIF برای برگرداندن تعداد ماه های کامل بین ماه ورودی و ماه رها کردن استفاده می شود. چیزی که در نهایت به دست می آورید، شمارش تعداد ماه هایی است که باید برای دانش آموز صورتحساب شود.
