Search dates - return number of days

[dunmore83]

Hi,

I have the following scenario I need some help with:!

- i need to search row B4:AG4 for dates entered (i.e 12-Aug-11) & count the number of times a different date is found

- in cell B1 i need it to return the number of times a date in B4:BG4 is found

Thanks so much for your help!!

 

[VoG]

To count the number of times a particular date is found try like this

=COUNTIF(B4:BG4,"28/8/2011"+0)

 

[dunmore83]

Thanks for the quick reply.

Is this formula only searching the range for the specific date "28/8/2011"???

I need it to search the range & return the number of times a date is entered in the cells in this range
i.e. cell H4 has 15-Aug-2011, cell N4 has 17-Aug-2011, cell W4 has 22-Aug-2011.... no other dates found in the range
- formula should return 3 (i.e. 3 dates found)

Thanks in advance for your help!!!

 

[VoG]

If the range can only contain dates, blanks or text try

COUNT(B4:BG4)

 

[dunmore83]

No the range also contains days based on the date
i.e. cell I4 is MON based on cell H4 which is 15-Aug-11

Any other suggestions???

Can i combine dates & days into one formula???
i.e. cell I4 could return Mon 15-Aug-11

Thanks for your help!!!

 

[VoG]

Sorry, I don't really understand what you are trying to count. the COUNT formula wouldn't include MON which is text.

To display the day plus date in I4 use

=H4

and format I4 as Custom

ddd dd-mmm-yyyy

 

[dunmore83]

Thanks & thanks. Your suggestion COUNT(B4:BG4) worked. I thought it would also count the text!

In regards to the custom date format ddd dd-mmm-yyyy is there any way to make the letters in CAPITALS??? I have searched this on google before & have been unable to find a solution.

Cheers!

 

[VoG]

The only way I know to do that is

=UPPER(TEXT(H4,"ddd"))&" "&TEXT(H4,"dd-mmm-yyyy")

but the resulting value is text, not a date.

 

[dunmore83]

so if i have this formula in cell K4..... =IF(I10="YES",H4+1+(WEEKDAY(H4)=6)*2,"N/A")

how would i apply your suggestion to make the date in K4 appear in CAPITALS???

.... =UPPER(TEXT(H4,"ddd"))&" "&TEXT(H4,"dd-mmm-yyyy")

is their a way to edit the format to make capitals i.e ddd dd-mmm-yyyy ???

thanks!