Formaula to calculate the downtime monthwise

I think that part of the issue is that some of your "dates" aren't actually dates at all, but strings that look like dates. You may have to translate them using a formula like =IF(ISNUMBER(C2),C1,DATEVALUE(C2)).

The attached will outline the logic. You may have to change things to suit your particular needs.

The first thing I did was convert the data into an Excel Table. I am taking advantage of two of the things tables provide:
1. You can use column headers in formulas instead of ranges. This makes the formulas easier to read.
2. Tables know how many rows they have. So you do not have to include the entire column or guess at how many rows to include

To get more information on how to work with Excel Tables, see this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Here are some other modifications I added:

You need a place to specify the start date and the end date. I set these up in Cells B1 and B2 on the Monthwise downtime sheet. I also gave them a name. To give a name to a cell, put the cursor in the cell and go to the upper left where the cell address is shown. Then type in the name and press Enter. I gave these two cells the names: Start_Date and End_Date respectively.

I also added a helper column to the data called In Range. The formula is =AND([@[Issue Reported Date/Time]]>=Start_Date,[@[Issue Reported Date/Time]]<=End_Date). This formula is True if the date is within range and false if the date is out of range.

Now to the logic. Each cell in the equipment downtime area (Cells B6:E34) is subject to 3 criteria:
1. It must match the month and year of the header row (Row 5).
2. It must be between the start date and the end date.
3. It must match the equipment alias name.

I decided to use SUMPRODUCT instead of SUMIFS mainly because it's easier to use formulas like MONTH() and it is easier to use inequalities.

So this part of the formula:
(MONTH(Table_Data[Issue Reported Date/Time]) = MONTH(B$5)) * (YEAR(Table_Data[Issue Reported Date/Time]) = YEAR(B$5))
assures that the value we are looking for is the same month and year as the header.

This part of the formula:
(Table_Data[In Range] = TRUE)
assures that the date is within range.

The combination of these two parts takes care of requirement that the start date and end date may be in the middle of the month instead of at the beginning and end of the month and yet accounts for full months in between.

The final part of the formula is:
(Table_Data[Equipment Alias Name]=$A6)
assures that the calculated value is for the specific piece of equipment.

To get a better idea of how SUMPRODUCT works, see this article: http://www.utteraccess.com/wiki/inde...Array_Formulas. SUMPRODUCT is not an array formula (so you do not have to press CTRL-SHIFT-ENTER), but it acts like one.

Some of the other things I noticed was that you had some zeros in Column A. You may want to wrap the formulas in an if statement if special handling is required for these values.

Hopefully, this should get you started.