count if unique date

Caleb W

I am using a check box in an install list that once the box is checked it records a date. Then using the formula =IF(ISDATE([Install Date]@row), COUNT(DISTINCT([Install Date]$2:[Install Date]2)), "--") to assign the day or days that we have installed in total. SO if we install 15pcs on 9/20/23 than all 15 will show 1. The pcs installed on 9/21/23 would show 2. My struggle is, when we have to skip out of order due to issues onsite the numbers get a little messy. How would I adjust my formula to capture this?

Genevieve P.

Hey @Caleb W

Can you explain what you mean when you say "skip out of order"? Do you mean that "Installed" is checked and a date is recorded, but that it isn't actually installed on that date?

Is there somewhere else that the updated date is recorded?

Caleb W

@Genevieve P.

In scenario where the pieces do not install per the order the sheet is in and we have to skip down a few boxes. The dates stamps come in and show the correct date the piece was installed, but then the install day gets off. In the example below, the 6/07 was our 3rd day of install, but then you can see we skipped a few due to site issues and set them the next day on 6/08, which then changed everything from there down to day 4, even the remaining pcs that should still say day 3 "6/07".

Genevieve P.

Hi @Caleb W

Thank you for this screen capture, it helps! So yes, the count changes because your formula is based on row order - you're looking from row 2 to the current row, including everything in between. When a date is unique in that range, it's counted.

If we knew a bit more about your current process, there may be a different formula we could use. For example, you could count how many Working Days are between a set start date and the current date in that cell:

=NETWORKDAYS([Install Date]$2, [Install Date]@row)

See: NETWORKDAYS Function

Of course, this won't exclude any other dates when you have big gaps, e.g. a week between dates.

Another option would be to use a Report to Group the rows based on the Date and then use the Summary feature to Count the unique dates. See: Group data to organize results in report builder

Cheers,

Genevieve

Caleb W

Thank you @Genevieve P.

Right now our current process is this:

As our field crew installs pcs they check the box that shows the pcs is installed, that then automates the date stamp allowing us to track what is installed each day.

The install day is to track how many day we are installing and what pcs. That data then counts total days of install to coordinate in to a production per day rate.

Which is then used to help us capture actual per day cost onsite and also job projections.

I understand using a report to capture the per day install, but can then can the report be used to feed my other sheets?

Genevieve P.

Hey @Caleb W

No, a Report cannot link out to other sheets. You're correct in that you'd need a formula in the cell of a sheet to link out.

For your total distinct days your current formula should work, but it cannot identify that certain days shouldn't be counted yet if they're in a different order, as you've found. The minute you have a distinctly new date within the selected range (based on row number), the count will go up.

Caleb W

@Genevieve P. Thank you for helping out on this.