Please help. I have to calculate the machine downtime between start date and end date between 9:00-18:00, assuming that there is no holiday and includes weekdays.
What I did:
1. Calculated the time difference between START DATE/TIME (Column A) and END DATE/TIME (COLUMN B) which results to TIME DIFFERENCE (Column C) using formula (B-A)*24.
2. I have found the formula to solve Hours and Minutes within the working hours of 9AM-6PM. Using this formula
=(NETWORKDAYS(A11,B11)-1)*("18:00"-"9:00")+IF(NETWORKDAYS(B11,B11),MEDIAN(MOD(B11,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS(A11,A11)*MOD(A11,1),"18:00","9:00")
The problem is NETWORKDAYS excluded the WEEKENDS. As you can see in the table below, the time difference of June 14, 2019 20:32 and June 17 10:28 is only 1 hour and 28 minutes since June 14 is Friday past the time range of 9:00 - 18:00 and it only includes June 17, Monday 9:00 - 10:28. I need the weekends included in the result with the same time range.
<tbody>
</tbody>
I have searched for formulas with the weekends included but haven't found any. I have 500+ entries with different dates and time in excel. It will be awesome if you could help me with this.
Thank you in Advance!
What I did:
1. Calculated the time difference between START DATE/TIME (Column A) and END DATE/TIME (COLUMN B) which results to TIME DIFFERENCE (Column C) using formula (B-A)*24.
2. I have found the formula to solve Hours and Minutes within the working hours of 9AM-6PM. Using this formula
=(NETWORKDAYS(A11,B11)-1)*("18:00"-"9:00")+IF(NETWORKDAYS(B11,B11),MEDIAN(MOD(B11,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS(A11,A11)*MOD(A11,1),"18:00","9:00")
The problem is NETWORKDAYS excluded the WEEKENDS. As you can see in the table below, the time difference of June 14, 2019 20:32 and June 17 10:28 is only 1 hour and 28 minutes since June 14 is Friday past the time range of 9:00 - 18:00 and it only includes June 17, Monday 9:00 - 10:28. I need the weekends included in the result with the same time range.
A | B | C | |||
START TIME/DATE | END TIME/DATE | TIME DIFFERENCE | |||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
I have searched for formulas with the weekends included but haven't found any. I have 500+ entries with different dates and time in excel. It will be awesome if you could help me with this.
Thank you in Advance!