پرسش تشخیص بازه زمانی

[mahdi63]

با سلام خدمت اساتید معزز
a - من در دو سلول شروع و پایان یک بازه زمانی رو مشخص کردم مثلا در یک سلول ساعت 8:00 و در یک سلول دیگه 15:00
b- و در چهار سلول دیگه دو بازه زمانی دیگه هم ثبت می شه مثلا در دو سلول اول زمان های 8:00 و 12:00 و در دو سلول دوم زمان های 14:00 و 18:00 وارد می شه (که البته این زمان ها متغییر هستند و این زمان ها صرفا برای مثال است و چه بسا اصلا دو سلول دوم اصلا اطلاعاتی نداشته باشه)
چطور میشه فرمولی تعریف کرد که تشخیص بده چه مقدار از زمان های وارد شده در مرحله b دربازه زمانی مرحله a وارد است و چقدر هم خارج از آن است؟

[Amir Ghasemiyan]

با سلام خدمت اساتید معزز
a - من در دو سلول شروع و پایان یک بازه زمانی رو مشخص کردم مثلا در یک سلول ساعت 8:00 و در یک سلول دیگه 15:00
b- و در چهار سلول دیگه دو بازه زمانی دیگه هم ثبت می شه مثلا در دو سلول اول زمان های 8:00 و 12:00 و در دو سلول دوم زمان های 14:00 و 18:00 وارد می شه (که البته این زمان ها متغییر هستند و این زمان ها صرفا برای مثال است و چه بسا اصلا دو سلول دوم اصلا اطلاعاتی نداشته باشه)
چطور میشه فرمولی تعریف کرد که تشخیص بده چه مقدار از زمان های وارد شده در مرحله b دربازه زمانی مرحله a وارد است و چقدر هم خارج از آن است؟

سلام دوست عزيز
فرض كنيد بازه زماني مرجع در سلول هاي a1 و b1 نوشته شده و بازه زماني شما در سلول هاي a3 و b3
براي اينكه ببينيم چقدر خارج از بازه زماني مرجع هست از اين فرمول استفاده كنيد:

=if(a3<a1,a1-a3,0)+if(b1<b3,b3-b1,0)

براي اينكه بدانيد چقدر داخل بازه زماني هست از اين فرمول:

=if(b1<b3,b1,b3)-if(a3<a1,a1,a3)

[mahdi63]

سلام دوست عزيز
فرض كنيد بازه زماني مرجع در سلول هاي a1 و b1 نوشته شده و بازه زماني شما در سلول هاي a3 و b3
براي اينكه ببينيم چقدر خارج از بازه زماني مرجع هست از اين فرمول استفاده كنيد:

=if(a3<a1,a1-a3,0)+if(b1<b3,b3-b1,0)

براي اينكه بدانيد چقدر داخل بازه زماني هست از اين فرمول:

=if(b1<b3,b1,b3)-if(a3<a1,a1,a3)

با سلام و تشکر از پاسخ شما
همونطور که در پست اول اشاره کردم مشکل من در محاسبه داخل بودن یا خارج بودن زمان در چهار سلول زمانی است یعنی بررسی زمان دو بازه زمانی با توجه به یک بازه زمانی اولیه. مثلا:
بازه زمانی الگو یا اولیه که قرار است بازه ملاک ما برای تشخیص باشد عبارت است:
a1 08:00
b1 15:00
و سپس دو بازه زمانی دیگه داریم که میخوایم بررسی بشه که این بازه ها در مجموع چند ساعت در بازه زمانی اصلی قرار دارد و چند ساعت خارج از آن
a3 08:00
b3 12:15
a4 13:05
b4 16:55
که در مثال ذکر شده مجموعا 01:55 ساعت در خارج از بازه زمانی اولیه است و 06:10 ساعت در بازه زمانی اولیه قرار گرفته است(مجموع دو بازه زمانی آخر که جهت مقایسه ثبت شده 08:05 است)
فرمولی لازم دارم که این مقایسه رو انجام بده
پاسخ قبلی برای مقایسه یک بازه زمانی با بازه زمانی اولیه و اصلی است و من مقایسه دو بازه زمانی با بازه زمانی اولیه و اصلی رو می خوام

[Amir Ghasemiyan]

با سلام و تشکر از پاسخ شما
همونطور که در پست اول اشاره کردم مشکل من در محاسبه داخل بودن یا خارج بودن زمان در چهار سلول زمانی است یعنی بررسی زمان دو بازه زمانی با توجه به یک بازه زمانی اولیه. مثلا:
بازه زمانی الگو یا اولیه که قرار است بازه ملاک ما برای تشخیص باشد عبارت است:
A1 08:00
b1 15:00
و سپس دو بازه زمانی دیگه داریم که میخوایم بررسی بشه که این بازه ها در مجموع چند ساعت در بازه زمانی اصلی قرار دارد و چند ساعت خارج از آن
a3 08:00
b3 12:15
a4 13:05
b4 16:55
که در مثال ذکر شده مجموعا 01:55 ساعت در خارج از بازه زمانی اولیه است و 06:10 ساعت در بازه زمانی اولیه قرار گرفته است(مجموع دو بازه زمانی آخر که جهت مقایسه ثبت شده 08:05 است)
فرمولی لازم دارم که این مقایسه رو انجام بده
پاسخ قبلی برای مقایسه یک بازه زمانی با بازه زمانی اولیه و اصلی است و من مقایسه دو بازه زمانی با بازه زمانی اولیه و اصلی رو می خوام

دوست عزيز فرمولي كه من عرض كردم روند محاسبه رو مشخص ميكنه. شما كافيه همين فرمول رو براي رديف 4 هم بنويسيد تا جواب نهايي رو داشته باشيد. من زحمتتون رو كم ميكنم و فرمول رو تقديم ميكنم

اين فرمول براي محاسبه مقدار خارج از محدوده

=if(a3<a1,a1-a3,0)+if(b1<b3,b3-b1,0)+if(a4<a1,a1-a4,0)+if(b1<b4,b4-b1,0)

و اين فرمول هم براي محاسبه داخل محدوده:

=if(b1<b3,b1,b3)-if(a3<a1,a1,a3)+if(b1<b4,b1,b4)-if(a4<a1,a1,a4)

[mahdi63]

دوست عزيز فرمولي كه من عرض كردم روند محاسبه رو مشخص ميكنه. شما كافيه همين فرمول رو براي رديف 4 هم بنويسيد تا جواب نهايي رو داشته باشيد. من زحمتتون رو كم ميكنم و فرمول رو تقديم ميكنم

اين فرمول براي محاسبه مقدار خارج از محدوده

=if(a3<a1,a1-a3,0)+if(b1<b3,b3-b1,0)+if(a4<a1,a1-a4,0)+if(b1<b4,b4-b1,0)

و اين فرمول هم براي محاسبه داخل محدوده:

=if(b1<b3,b1,b3)-if(a3<a1,a1,a3)+if(b1<b4,b1,b4)-if(a4<a1,a1,a4)

با سلام مجدد و تشکر از پاسخ شما
بله ملتفت موضوع هستم که با جمع فرمول مربوط به بازه زمانی اول و دوم به نتیجه می رسیم اما طبق عرض بنده در کامنت اول، بازه های زمانی متغییر هستند لذا ممکن است گاهی بازه زمانی دوم یا بازه زمانی سوم خالی باشد؛ در این صورت این فرمول به درستی عمل نمی کند و نیاز به اصلاح دارد که بنده تغییراتی اعمال کردم و نظر شما رو در این باره خواهانم که اگه راه ساده تری وجود داره اعمال کنم

[Amir Ghasemiyan]

با سلام مجدد و تشکر از پاسخ شما
بله ملتفت موضوع هستم که با جمع فرمول مربوط به بازه زمانی اول و دوم به نتیجه می رسیم اما طبق عرض بنده در کامنت اول، بازه های زمانی متغییر هستند لذا ممکن است گاهی بازه زمانی دوم یا بازه زمانی سوم خالی باشد؛ در این صورت این فرمول به درستی عمل نمی کند و نیاز به اصلاح دارد که بنده تغییراتی اعمال کردم و نظر شما رو در این باره خواهانم که اگه راه ساده تری وجود داره اعمال کنم

چه اصلاحی دوست عزیز؟
من سطر چهارم رو کامل پاک کردم. هیچ خللی در فرمول ایجاد نشد

[mahdi63]

چه اصلاحی دوست عزیز؟
من سطر چهارم رو کامل پاک کردم. هیچ خللی در فرمول ایجاد نشد

سطر چهارم؟!؟!؟!؟!؟!؟
اگه منظورتون ردیف چهارمه (بازه زمانی آخر) که با فرمول شما (اگرچه فرمولتون کاملا صحیحه اما به خاطر نواقصی که داشت) محاسبه کاملا به هم می ریخت اما اگه در فایل ارسالی بنده انجام دادید بله درسته و مشکلی نداره لکن به خاطر تو در تو شدن ifها خواستم بدونم که راه دیگه ای وجود داره؟ چون اگه این فرمول رو روی ردیف ها متعدد اعمال کنم دچار افت سرعت می شم.
لطفا اگه پاک کردید، تست کنید که اگه یکی از بازه های دوم یا سوم وارد نشد (خالی بماند) خللی ایجاد نمیشه و اگه خللی ایجاد نشد فایل رو بذارید استفاده کنم

[Amir Ghasemiyan]

سطر چهارم؟!؟!؟!؟!؟!؟
اگه منظورتون ردیف چهارمه (بازه زمانی آخر) که با فرمول شما (اگرچه فرمولتون کاملا صحیحه اما به خاطر نواقصی که داشت) محاسبه کاملا به هم می ریخت اما اگه در فایل ارسالی بنده انجام دادید بله درسته و مشکلی نداره لکن به خاطر تو در تو شدن ifها خواستم بدونم که راه دیگه ای وجود داره؟ چون اگه این فرمول رو روی ردیف ها متعدد اعمال کنم دچار افت سرعت می شم.
لطفا اگه پاک کردید، تست کنید که اگه یکی از بازه های دوم یا سوم وارد نشد (خالی بماند) خللی ایجاد نمیشه و اگه خللی ایجاد نشد فایل رو بذارید استفاده کنم

سطر با رديف چه فرقي داره؟ يك مفهموم هست

بهترين راهي كه به ذهنم ميرسه كه به if تو در تو نخوريم اين فرمول هست:
براي خارج از محدوده:

=IF(AND(A3>0,B3>0),MAX(B1,B3)-B1+A1-MIN(A1,A3),0)+IF(AND(A4>0,B4>0),MAX(B1,B4)-B1+A1-MIN(A1,A4),0)

براي داخل محدوده:

=IF(AND(A3>0,B3>0),MIN(B1,B3)-MAX(A1,A3),0)+IF(AND(A4>0,B4>0),MIN(B1,B4)-MAX(A1,A4),0)

درضمن فرمول شما هم كاملا صحيح نبود. اگر ساعت خروج ثبت بشه اما ورود ثبت نشده باشه نبايد در نظر گرفته بشه

[mahdi63]

سطر با رديف چه فرقي داره؟ يك مفهموم هست

بهترين راهي كه به ذهنم ميرسه كه به if تو در تو نخوريم اين فرمول هست:
براي خارج از محدوده:

=IF(AND(A3>0,B3>0),MAX(B1,B3)-B1+A1-MIN(A1,A3),0)+IF(AND(A4>0,B4>0),MAX(B1,B4)-B1+A1-MIN(A1,A4),0)

براي داخل محدوده:

=IF(AND(A3>0,B3>0),MIN(B1,B3)-MAX(A1,A3),0)+IF(AND(A4>0,B4>0),MIN(B1,B4)-MAX(A1,A4),0)

درضمن فرمول شما هم كاملا صحيح نبود. اگر ساعت خروج ثبت بشه اما ورود ثبت نشده باشه نبايد در نظر گرفته بشه

سلام و سپاس از زحمات شما
کد خیلی خوب شد اما ظاهرا برای تشخیص زمان خارج در محدوده درست عمل نمی کنه(با پارامترهای مختلف تست زدم با برخی مقادیر نتیجه صحیحه اما با برخی موارد نتیجه نادرست)
من نتونستم کاریش کنم در صورت امکان نگاهی بیندازید و بررسی کنید