Excel Formula:

`=IF(D8="","",INT(D8))`

Excel Formula:

`=SUMPRODUCT((S:S<>"")/COUNTIF(S:S,S:S&""))`

I'm looking to possibly expand/alter this formula to incorporate one or two scenarios in order to minimize scrolling down and cutting out certain rows to allow the formula to function. When the data is copied/pasted from the exported file, it repeats headers and such when employee data spans multiple pages, so you might have gaps in rows that actually have dates and then other rows which have these headers, have the INT formula above yielding #Value errors. These #Value errors are preventing the formula in T3 from being able to calculate the count of the unique dates. What I then have to do is highlight every group of rows that have headers and delete them until there are no more #Value errors. I am wondering if the formula in T3 can somehow be altered to simply ignore those errors that way no deleting is necessary?

Secondly, there may be instances where certain rows have additional info which as I mentioned above would be located in Column N. These would say either "Holiday" or "Birthday". Here's where it gets a little tricky. First off, if you notice on the spreadsheet, it is possible to have the same date listed more than once. It could be for a couple of reasons: 1.) The employee worked that day but is being paid two different rates of pay, or 2.) they employee worked on a Holiday and is being paid for his worked hours and the holiday hours but eh holiday hours will show separately. In either case, the date should only be counted once in the formula which it does do. However, there may be instances where an employee does NOT work on the holiday and simply receives the holiday pay. The date is therefore listed in the data. The thing is though, in that instance, the holiday is not an actual worked day since they took the day off and just received pay for it, so it shouldn't actually be counted in the worked days formula.

So all in all, I am wondering if the formula in T3 can be altered to encompass both of those scenarios: ignoring the #value error, and then also ignoring counting the date if it is the ONLY date listed and has either holiday or birthday in column N. In the attachment, this person has 3 entries for the holiday on 9/6: 1.) he worked the holiday, 2.) was paid the holiday hours which show separate, 3.) and he was also paid a different rate for some hours worked(a differential). Before altering any formula, this is actually calculating correctly and only counting that date once but only because there are worked hours there. If we were to take 2 of those entries out and leave only the holiday entry, the formula is still counting that date as a worked date which it should not. I hope that helps to kinda clarify anything.

Also posted at

### Expanding on a formula that counts unique dates [SOLVED]

Hi, I had a previous situation where I needed to count unique dates for employees number of days worked. The data is exported from a program to an Excel sheet which we then copy and paste into a template that has formulas to count these. I have attached the template for reference. Columns A thru...

www.excelforum.com

No solution as of yet.

Here is also an illustration for reference: