Need to accomplish 2 things with Dates in a Column.

[TJ Saulnier]

I have a large spread sheet (44K rows) and I need to do two things:

1 - Highlight dates that are sequential in the column ( B4:B44000 ) the column has blanks that separate chunks of the data I would like to use conditional formatting to highlight those dates that are sequential (in the example below you'll notice there are a few pairs of days that meet this criteria (ie:sept. 19,20). If it's possible to use different colors to show 2 or 3 or more day groupings that would be spectacular.

2 - I need to count the number of Each appearance of a day of the week, so within the same column How many are Monday, how many are Tuesday and so on. I'm assuming that I can use a countif type formula but the data is in date format so I'm not entirely sure how to extract the day of the week from the number that is there now. 41369 translates to Friday, April 05, 2013 when the cell is formated for long date but I can't figure out how to lift the day of the week from that number.

the Column looks like this where Nominal Date is the header in B3 and the blank appears at B33 :


Nominal Date
Saturday, March 12, 2011
Sunday, April 17, 2011
Sunday, May 29, 2011
Thursday, August 04, 2011
Thursday, August 18, 2011
Thursday, September 01, 2011
Monday, September 19, 2011
Tuesday, September 20, 2011
Thursday, November 17, 2011
Monday, November 21, 2011
Tuesday, November 22, 2011
Friday, December 09, 2011
Monday, December 12, 2011
Thursday, January 19, 2012
Sunday, March 18, 2012
Wednesday, April 25, 2012
Thursday, April 26, 2012
Sunday, May 13, 2012
Thursday, June 07, 2012
Thursday, October 04, 2012
Friday, October 05, 2012
Monday, October 08, 2012
Tuesday, October 09, 2012
Wednesday, October 10, 2012
Thursday, October 11, 2012
Friday, October 12, 2012
Monday, October 15, 2012
Tuesday, October 16, 2012
Tuesday, June 11, 2013

Monday, February 25, 2008
Monday, April 21, 2008
Thursday, July 17, 2008
Friday, July 18, 2008
Monday, July 21, 2008
Thursday, September 11, 2008
Tuesday, September 23, 2008
Wednesday, September 24, 2008

[FDibbins]

for question 1, assuming your dates ARE dates,

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =B4+1=B5 format fill what color you want

Then for every extra day you want to test for...
=B4+2=B6
=B4+3=B7
etc

[FDibbins]

for the 2nd part, try this...

Add a helper column (I used G), you can hide it if you want. Then use this, copied down...
=WEEKDAY(B4,2)
Next, use this to count each day of the week...
=COUNTIF($G$4:$G$17,1) this will give you Monday
repeat for each day, changing the ,1) part or ,2) ,3) etc

[TJ Saulnier]

Wow, it never ceases to amaze me how simple some of these solutions are when you see them. Eveyone I've encountered in this forum has been exceptionally helpful. Given the number of your posts FDibbins I'm guessing you've been at this a while. If you would indulge me once more ...

how can I account for the blanks in the G helper column... the =weekday function puts a 6 when there is no data which is making the Saturday count wrong. and for the conditional formatting is there a way to combine the formulae such that it highlights all of the days that appear in sequence in the same color (2 consecutive days would both be painted the same color)

[Pepe Le Mokko]

You can amend the weekday part as follows

Please Login or Register  to view this content.

[TJ Saulnier]

Thanks Pepe that works perfectly.

[FDibbins]

Thanks for the back-up Pepe, I was AFK

[Pepe Le Mokko]

What's AFK ?

[TJ Saulnier]

Away From Keyboard