Formula to count unique dates or NOT count based on criteria

bh24524

Active Member
Joined
Dec 11, 2008
Messages
365
Office Version
  1. 365
  2. 2007
Hello, 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 also attached an image of the sheet since I can't actually access XL2BB in this location. Columns A thru N are what contain the data to be copied/pasted. Column N is actually generally blank and only every once in a while will it have data in it(That will actually be referred to later on though). In column S and starting in Row 8, I have the formula:
Excel Formula:
=IF(D8="","",INT(D8))
Dates are contained in cells D8 and onward so this formula is converting them to integers in order that they can be counted. The formula also carries down to row 300 just to allow for lots of data to be pasted if necessary. In T3, I have the following formula:
Excel Formula:
=SUMPRODUCT((S:S<>"")/COUNTIF(S:S,S:S&""))
and that will count the number of unique integers contained in column 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

No solution as of yet.

Here is also an illustration for reference:
1633718211792.png
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I did go ahead and edit the INT formula to
Excel Formula:
=IFERROR(IF(D8="","",INT(D8)),"")
That takes care of the #value error message and allows the formula to count. The second part though where it excludes counting Holiday and birthday dates if that date is the only occurrence is still a problem I am not sure of.
 
Upvote 0
Alternatively, I am open to having VBA automatically delete the set of rows if it sees either Birthday or Holiday somewhere in column N. So if for example N46 happens to have Holiday in its cell, row 46 should be deleted shifting cells up. If VBA could look for and detect these occurrences automatically and delete them, it should allow the formula to be accurate. I'm just not nearly as familiar with VBA and the coding to use is the only thing.
 
Upvote 0

Forum statistics

Threads
1,222,103
Messages
6,163,947
Members
451,867
Latest member
csktwyr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top