Counting unique Dates without the time affecting the outcome

[zanzi]

I am trying to count the number of days worked in a list the has different activities listed at different times in the day. Because of the different times in a single day I am having trouble getting a count of the days only.

I used a formula from another posting ......

=SUM(IF(LEN(B29:B100),1/COUNTIF(B29:B100,B29:B100)))

but it treats each date and time as a unique entity. I need to just count the days but am stuck with data that also has times attached. Is there any way around this?

example

4/12/04 12:00 AM
4/12/04 12:00 AM
4/13/04 12:00 AM
4/13/04 5:57 PM
4/13/04 8:37 PM
4/14/04 12:00 AM
4/14/04 6:26 PM
4/14/04 6:28 PM
4/14/04 6:28 PM
4/15/04 12:00 AM
4/15/04 7:29 PM
4/15/04 7:29 PM
4/16/04 12:00 AM
4/16/04 8:37 PM
4/17/04 12:00 AM
4/17/04 3:52 PM

 

[Todd Bardoni]

Re: Counting unique Dates without the time affecting the out

Try:-

=TEXT(A1,"mm/dd/yyyy")

in your formula to covert it to a date only.

 

[Dick Kusleika]

Re: Counting unique Dates without the time affecting the out

tbardoni

Try this one

=SUM(IF(LEN(B29:B100),1/(COUNTIF(B29:B100,"<"&INT(B29:B100)+1)-COUNTIF(B29:B100,"<"&INT(B29:B100)))))

 

[zanzi]

Re: Counting unique Dates without the time affecting the out

Thanks

Where should I insert your formula ?

 

[Todd Bardoni]

Re: Counting unique Dates without the time affecting the out

Sure, just give him the answer why don't ya.

Looks good.

 

[Todd Bardoni]

Re: Counting unique Dates without the time affecting the out

Thanks

Where should I insert your formula ?

Hey hey! We don't tolerate that kind of talk around here.

 

[Dick Kusleika]

Re: Counting unique Dates without the time affecting the out

Sorry tbardoni, I meant to address that to zanzi. It's my first day, and I haven't figured out that I should refresh my browser once in a while.

 

[jlu21]

Re: Counting unique Dates without the time affecting the out

Hi,

Does anyone have any suggestions if the range is not fixed? ie. in the example above, if you're passed a start date and an end date, how many unique days are there between 4/13/04 and 4/15/04? How many between 4/14/04 and 4/17/04? etc.

 

[Aladin Akyurek]

Re: Counting unique Dates without the time affecting the out

Hi,

Does anyone have any suggestions if the range is not fixed? ie. in the example above, if you're passed a start date and an end date, how many unique days are there between 4/13/04 and 4/15/04? How many between 4/14/04 and 4/17/04? etc.

Question 1: What do you have -- date/time values or just date values?

Question 2: Are the values sorted in ascending order?

 

[jlu21]

Re: Counting unique Dates without the time affecting the out

Only date values. (mm/dd/yyyy)

The data can be sorted in ascending value, but I am trying to figure out a solution flexible enough to accomodate it if one or two date records are out of order.

I kinda have a rough solution, combining the code provided above, and also using the MATCH function to determine the first and last row ids of the range, and then concatenating the fields to create a range, but it's not very clean, and I'm sure there are situations where it may not be accurate. (ie. when the data is not in ascending order)