How to countif columns of dates

[mrgillus]

I have three seperate Formulas on a summary page that will count dates depending on whether they will be approaching in the next month, whether todays date falls within them and if they are expired or are less than todays date.

Basically the form was developed to monitor a maintanence schedule and give a summary to the person in charge of cleaning so they can forecast there work load. I have managed to get the formula working for "whether todays date falls within them" ("# of Computers than can be cleaned today" as it appears on the summary form) but I cannot seem to get the other formulas figured out.. Well at least the formula that checks to see which computers are past due.

As far as the formula that checks to see which computers will need to be cleaned within the next month, I don't know how to make that work at all considering the setup of my Date input page.

Let me know what additional questions you may have. .And thanks for all the help!

[NBVC]

Do you mean?

=COUNTIFS(From,"<"&TODAY()+30,TO,">"&TODAY())

which checks for anything where the TO column is greater than today and the From column is less than Today + 30 or one month from today.

[mrgillus]

My apologies as I forgot to mention where the information needed to come from for the Month count.. The data will need to come from the Input page and will need to look to see if any of the people listed will be off within the next month AND filter out the true's there based on whether the computer is within the 120 day cleaning schedule on the schedule sheet..

I was thinking about making a helper column on the Input sheet that would check each row (each person's dates that are listed) and if any of those dates would fall within the next 30 days, AND their computer will need to be cleaned within the next 30 days (this needs to be checked from the Schedule sheet) then the helper column would return true.
Then on the Summary sheet, the formula would just count all the trues that appear in the helper column located on the Off Days input sheet.

Let me know if this makes any more since- Thanks again!

[NBVC]

You can do that, yes...

.. you can probably also add the person name check to the countifs formula too.

e.g


=COUNTIFS(Names,A1,From,"<"&TODAY()+30,TO,">"&TODAY())

Where Names is the range name for the column containing names and A1 in the active sheet contains the name to look for in the active sheet.

[mrgillus]

Thanks for the help NBVC.

However, what would be the look of this if I didn't want to choose a name to check but instead just wanted to get a total between everybody? Or is this possible through a worksheet function?

Otherwise I tried to make this formula work and was unsuccessful..

I applied the formula and reattached the example sheet

[NBVC]

Well, the first formula I gave was indifferent to the names so it was between everybody...

In your formula you referenced Names to the wrong range, it should be referencing the range in the Shedule Sheet and range size should be the same..

so change Names named range to reference: =Schedule!$A$3:$A$91

If I am misunderstanding, please post sample of expected results and show why.

[mrgillus]

I have attached another example- it has some text boxes that may help explain the intended results a bit better..

[NBVC]

Try, in A3:

Please Login or Register  to view this content.

copied down.

Your formula in D9 of Summary sheet would then be:

=COUNTIF('Off Days input'!A3:A65,TRUE)

Note: No quotes around TRUE

[mrgillus]

It works minus it is not filtering out the computers that were already cleaned. I am working on it now to try and resolve this. Let me know if you have any ideas as to why thats not working.. Ref. "Off Days input" sheet cell "a4" should be returning false..

New form uploaded with formulas entered

[NBVC]

Should be:

Please Login or Register  to view this content.

[mrgillus]

Yup, that did the trick.. Thanks a bunch for all your help!