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:
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:
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:
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:
