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

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

فرض کنید 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-ژان) وجود ندارد.

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

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