Calculate downtime per week/month/year

Hello everyone,

I need some help with an Excel sheet we use at work.
We maintain trains in operation, and are penalised whenever we have to stop one for repairs; this downtime is used to measure the "availability" of trains per week (monday to sunday), per month and per year.


Right now the Excel sheet we use only takes into account the date whenever the downtime started, and assigns all the downtime to its corresponding week/month/year.

This means that if a repair takes several weeks of downtime (500 hours across 3 weeks for example), all that time is only "counted" for the week in which the repair started, and as such will put 500 hours of downtime in week1, and nothing in week2 and week3.

I've been trying to find a formula to properly allocate the downtimes to their correct week/month/year to no avail.

I hope I explained myself clearly enough.
I have attached an Excel file of what I'm looking for.

Thank you very much for your time.

All best,

There is no real start/end work times, since it's subways, they work 24/7.

As for the second question, I am using ISOWEEKNUM and not WEEKNUM, which makes 03/09/2023 the last day of the week.

Originally Posted by JohnTopley

If work is 24/7 then week hours should be 168 not 133 ?

And being somewhat down level with Excel, I dont have ISOWEEKNUM so over to others!

However, your 31:20 is stll a "mystery" as how it is calculated: it implies 24 + 7:20 which which Excel shows in the formula bar.

My apologies, I should have specified that the "Downtime HOURS Total" is calculated exluding the time between midnight and 5am; it is actually on this very forum that I got help for that formula.

This is the reason why the times are different.