• فواصل تاریخ در اکسل

    در این پست چند روش برای کار با فواصل تاریخ توضیح داده شده است.

    آیا تاریخ در یک فاصله زمانی قرار دارد؟

    فرض کنید 3 تاریخ داریم -- تاریخ شروع، تاریخ پایان و تاریخ آزمون. ما می توانیم آزمایش کنیم که آیا تاریخ آزمون در فاصله بین تاریخ شروع و تاریخ پایان قرار دارد یا خیر. در این فرمول از سه سلول با نام استفاده می کنیم: TDate1 برای تاریخ شروع، TDate2 برای تاریخ پایان و TDate برای تاریخ تست. این فرمول TRUE یا FALSE را برمی گرداند و نشان می دهد که آیا TDate در بازه زمانی قرار می گیرد یا خیر.

    =AND((TDate>=MIN(TDate1,TDate2)),TDate<=MAX(TDate1,TDate2))

    به عنوان مثال اگر TDate1 1-Jan و TDate2 31-Jan باشد و TDate 15-Jan باشد، فرمول TRUE برمی گردد، که نشان می دهد TDate در بازه زمانی قرار می گیرد.

    در این فرمول مهم نیست که TDate1 زودتر از TDate2 باشد یا دیرتر.

     

    تعداد روزها در یک بازه زمانی و نه در دیگری

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

    در زیر خلاصه ای از انواع مختلف فاصله وجود دارد. مقادیر Dates روزهایی هستند که می خواهیم شمارش کنیم. مقادیر VDates روزهایی هستند که ما می خواهیم از بازه Dates حذف کنیم. پیچیدگی فرمول به این دلیل است که باید همه انواع فاصله را کنترل کند.

     

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

    Name Description
    Date1 تاریخ شروع بازه اصلی. فاصله اصلی تاریخ هایی است که می خواهیم کار کنیم.
    Date2 تاریخ پایان بازه اصلی.
    VDate1 تاریخ شروع فاصله محرومیت. فاصله حذف، تاریخ هایی است که می خواهیم از شمارش فاصله اصلی حذف کنیم.
    VDate2 تاریخ پایان فاصله محرومیت.
    NWRange لیستی از تاریخ تعطیلات در نسخه دوم فرمول استفاده می شود که از تابع NETWORKDAYS استفاده می کند.

    برای این فرمول، ما نیاز داریم که Date1 کمتر از (زودتر از) یا برابر با Date2 باشد، و VDate1 کمتر از (زودتر از) یا برابر با VDate2 باشد.

     

     

     
    Date1 Date2 VDate1 VDate2 Result Description
    1-Jan 31-Jan 10-Jan 20-Jan 20 20 روز بین 1 ژانویه و 9 ژانویه (9 روز) و 21 ژانویه و 31 ژانویه (11 روز) وجود دارد. 11 روز بین 10 ژانویه و 20 ژانویه از 31 روز بین 1 تا 31 ژانویه کم می شود. در این مثال، کل بازه حذف (VDates) در بازه اصلی (Dates) گنجانده شده است.
    10-Jan 20-Jan 1-Jan 31-Jan 0 در اینجا، کل بازه اصلی در بازه حذف گنجانده شده است. هیچ روزی بین 10 ژانویه و 20 ژانویه وجود ندارد که خارج از 1 ژانویه و 31 ژانویه باشد.
    1-Jan 15-Jan 10-Jan 20-Jan 9 در این حالت، بخش پایانی بازه اصلی (1-ژانویه تا 15-ژان) با قسمت ابتدایی بازه حذف (10-ژانویه تا 20-ژان) همپوشانی دارد. در بازه اصلی 9 روز (1 ژانویه تا 9 ژانویه) وجود دارد که با فاصله حذف همپوشانی ندارند.
    10-Jan 20-Jan 1-Jan 15-Jan 5 بخش پایانی بازه اصلی با فاصله حذف همپوشانی دارد. 5 روز (16 ژانویه تا 20 ژانویه) در فاصله اصلی وجود دارد که در بخش حذف قرار نمی گیرند.

    توجه داشته باشید که تاریخ های اینجا شامل است. 10 روز بین 1 تا 10 ژانویه وجود دارد. این یک روز با چیزی که از کم کردن خرما بدست می آورید متفاوت است.

    فرمول بالا روزهای آخر هفته را متفاوت از روزهای کاری در نظر نمی گیرد. به عبارت دیگر شنبه و یکشنبه در محاسبات لحاظ می شود. اگر می خواهید فقط روزهای هفته را بشمارید، به استثنای تعطیلات آخر هفته، از نسخه اصلاح شده زیر استفاده کنید، که تابع NETWORKDAYS را برای محاسبه تعداد روزهای کاری در فواصل زمانی فراخوانی می کند. این تابع نام دیگری را به ترکیب اضافه می کند. این نام، NWRange، به محدوده ای اشاره دارد که شامل لیستی از تعطیلات است. اگر از تعطیلات استفاده نمی کنید، می توانید این نام را به یک سلول خالی نشان دهید یا آن را به طور کامل از فرمول حذف کنید.

    =IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS(Date1,Date2,NWRange),
    IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND(Date1>=VDate1,Date2<=VDate2)),
    MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS(VDate1,VDate2,NWRange)),
    IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)),
    IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
    IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NWRange)),NA())))

    تابع NETWORKDAYS بخشی از افزونه بسته ابزار تحلیل است، بنابراین برای استفاده از این فرمول باید این ماژول را نصب کنید. برای اطلاعات بیشتر در مورد استفاده از فرمول ها برای ایجاد تاریخ تعطیلات، به صفحه تعطیلات مراجعه کنید.

    Tangent: دلیل اینکه سلول های نامگذاری شده VDate1 و VDate2 هستند این است که من در ابتدا این فرمول را برای یک برنامه زمانبندی تعطیلات نوشتم و V به "تعطیلات" اشاره دارد. البته، شما می توانید سلول های خود را هر چیزی که با برنامه شما کار می کند نام گذاری کنید، یا می توانید به سادگی از مراجع سلولی استفاده کنید.

     

    تعداد روزهای مشترک در دو بازه

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

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

    Name Description
    IDate1 تاریخ شروع اولین فاصله
    IDate2 تاریخ پایان اولین فاصله
    RDate1 تاریخ شروع بازه دوم
    RDate2 تاریخ پایان بازه دوم
    NWRange لیستی از تاریخ تعطیلات در نسخه دوم فرمول استفاده می شود که از تابع NETWORKDAYS استفاده می کند.

    برای این فرمول، ما نیاز داریم که IDate1 کمتر از (زودتر از) یا برابر با IDate2 باشد، و RDate1 کمتر از (زودتر از) یا برابر با RDate2 باشد. فرمول زیر تعداد روزهایی را که در هر دو بازه هستند برمی گرداند.

    =IF(OR(IDate2<RDate1,IDate1>RDate2),0,(MIN(IDate2,RDate2)-MAX(IDate1,RDate1)+1))

    در اینجا چند نمونه آورده شده است.

     

    IDate1 IDate2 RDate1 RDate2 Result Description
    1-Jan 31-Jan 10-Jan 20-Jan 11 11 روز مشترک در فواصل وجود دارد. از آنجایی که تاریخ های RD در شناسه ها قرار دارند، نتیجه تعداد روزهای بین 10 ژانویه و 20 ژانویه یا 11 روز است.
    10-Jan 20-Jan 1-Jan 31-Jan 11 از آنجایی که این یک فرمت شرط AND است، می‌توانیم تاریخ‌های بین IDates و RDates را معکوس کنیم و همان نتیجه بالا یعنی 11 روز را دریافت کنیم.
    1-Jan 15-Jan 10-Jan 20-Jan 6 در اینجا، 6 روز مشترک برای این دو بازه وجود دارد - تاریخ های 10 ژانویه تا 15 ژانویه در هر دو بازه قرار می گیرند.
    1-Jan 10-Jan 15-Jan 20-Jan 0 نتیجه در اینجا 0 است، زیرا هیچ تاریخی در بازه IDate (1-ژانویه تا 10-ژانویه) از سقوط در فاصله RDate (15-ژانویه تا 20-ژان) وجود ندارد.

    توجه داشته باشید که تاریخ های اینجا شامل است. 10 روز بین 1 تا 10 ژانویه وجود دارد. این یک روز با چیزی که از کم کردن خرما بدست می آورید متفاوت است.

    فرمول بالا روزهای آخر هفته را متفاوت از روزهای کاری در نظر نمی گیرد. به عبارت دیگر شنبه و یکشنبه در محاسبات لحاظ می شود. اگر می خواهید فقط روزهای هفته را بشمارید، به استثنای تعطیلات آخر هفته، از نسخه اصلاح شده زیر استفاده کنید، که تابع NETWORKDAYS را برای محاسبه تعداد روزهای کاری در فواصل زمانی فراخوانی می کند. این تابع نام دیگری را به ترکیب اضافه می کند. این نام، NWRange، به محدوده ای اشاره دارد که شامل لیستی از تعطیلات است. اگر از تعطیلات استفاده نمی کنید، می توانید این نام را به یک سلول خالی نشان دهید یا آن را به طور کامل از فرمول حذف کنید.

    =IF(OR(IDate2<RDate1,IDate1>RDate2),0, ABS(NETWORKDAYS(MIN(IDate2,RDate2),MAX(IDate1,RDate1),NWRange)))
    

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

    نکته پایانی: این فرمول‌ها برای نشان دادن مفهوم «فاصله‌های» تاریخ و نحوه رسیدگی به آن‌ها توسط محاسبات تاریخ اکسل طراحی شده‌اند. منطق‌دانان احتمالاً می‌توانند فرمول‌ها را با ادغام ANDها و ORها به اشکال ساده‌تر کاهش دهند. اما نکته اینجا تصویرسازی و آموزش است، نه ساده ترین فرمول ممکن.

    نظرات ارسال شده ارسال نظر جدید
    برای تبادل نظر، می بایست در سایت وارد شوید

    ورود به سایت
تماس سبد خرید بالا