ماهش می تواند بفهمد که چگونه تفاوت بین دو تاریخ و زمان را در چند دقیقه محاسبه کند. با این حال، او مایل است تفاوت را بر حسب دقیقه محاسبه کند، اما ساعات بین 5:00 بعد از ظهر تا 8:00 صبح و همچنین همه موارد بین 5:00 بعد از ظهر جمعه تا 8:00 صبح دوشنبه را حذف کند. به عنوان مثال، اگر اولین تاریخ 09/02/18 ساعت 6 بعد از ظهر و تاریخ پایان آن 9/02/19 ساعت 9:00 صبح باشد، نتیجه صحیح باید 60 دقیقه باشد. ماهش متعجب است که آیا این کار با یک فرمول امکان پذیر است؟
همانطور که واضح است، یک فرمول برای دستیابی به نتیجه مطلوب می تواند بسیار پیچیده باشد. بسیاری از مشترکین راه حل های مختلفی ارائه کردند، از جمله برخی عملکردهای عالی تعریف شده توسط کاربر. به جای تمرکز روی همه آنها، فکر کردم که فقط به ظریف ترین (کوتاه ترین) فرمول می پردازم و استفاده از آن را پیشنهاد می کنم.
فرض کنید تاریخ/زمان شروع شما در سلول A1 و تاریخ/زمان پایان در سلول B1 بوده است. با توجه به این موارد می توانید از فرمول زیر استفاده کنید:
=(NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")
+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"
,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)
*MOD(A1,1),"17:00","08:00")
این یک فرمول واحد است. زمان سپری شده را برمی گرداند. این بدان معنی است که شما باید سلول را برای نمایش زمان سپری شده فرمت کنید. اگر ترجیح می دهید نتیجه را به صورت یک عدد صحیح معمولی داشته باشید، در عوض باید از این نسخه از فرمول استفاده کنید:
=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")
+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"
,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)
*MOD(A1,1),"17:00","08:00"))*1440
تغییر (ضرب نتیجه اولیه در 1440) به جای یک زمان سپری شده، منجر به چند دقیقه می شود. مقدار 1440 از ضرب 60 در 24 برای بدست آوردن تعداد دقیقه در روز بدست می آید.
