Conflicking dates in formula
 

Using =HLOOKUP(999999999999,F2:AC2,1,1) I am obtaining a last date entered in
E2.
Must type date in DD/MM/YYYY format.

To add how many time a date (by month appears) Im using
=COUNTIF(F2:F174,"*.7.2006")

It appears to me a conflict of date formats - just not sure how to change so
both formulars read. Any Assistance?

Conflicking dates in formula
 

Try

=SUMPRODUCT(--(YEAR(F2:F174)=2006),--(MONTH(F2:F174)=7))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer1960" wrote in message
...
Using =HLOOKUP(999999999999,F2:AC2,1,1) I am obtaining a last date entered
in
E2.
Must type date in DD/MM/YYYY format.

To add how many time a date (by month appears) Im using
=COUNTIF(F2:F174,"*.7.2006")

It appears to me a conflict of date formats - just not sure how to change
so
both formulars read. Any Assistance?

Conflicking dates in formula
 

Thanks Bob that worked.

Can the =HLOOKUP(999999<F2:AC2,1,1) be written in another way so that date
does not have to be entered as DD/MM/YYYY but as DD.MM.YYYY?

Reason being I have other wsheets where the date entry is DD.MM.YYYY so
=COUNTIF(G3:G49,""*.7.2006) counts the dates. Alternatively can you suggest
another formular for these wsheets so the dates can be entered DD/MM/YYYY.

Allowing for dates to be entered in the same way across the workbook.

Thanks again
Jennifer

"Bob Phillips" wrote:

Try

=SUMPRODUCT(--(YEAR(F2:F174)=2006),--(MONTH(F2:F174)=7))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer1960" wrote in message
...
Using =HLOOKUP(999999999999,F2:AC2,1,1) I am obtaining a last date entered
in
E2.
Must type date in DD/MM/YYYY format.

To add how many time a date (by month appears) Im using
=COUNTIF(F2:F174,"*.7.2006")

It appears to me a conflict of date formats - just not sure how to change
so
both formulars read. Any Assistance?

Conflicking dates in formula
 

It sounds to me that where you have dates of DD.MM.YYY they are not real
dates, but text fields, where the COUNTIF would work. Then the HLOOKUP won't
work because it is testing numeric. Sounds a bit of a catch-22.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer1960" wrote in message
...
Thanks Bob that worked.

Can the =HLOOKUP(999999<F2:AC2,1,1) be written in another way so that date
does not have to be entered as DD/MM/YYYY but as DD.MM.YYYY?

Reason being I have other wsheets where the date entry is DD.MM.YYYY so
=COUNTIF(G3:G49,""*.7.2006) counts the dates. Alternatively can you
suggest
another formular for these wsheets so the dates can be entered DD/MM/YYYY.

Allowing for dates to be entered in the same way across the workbook.

Thanks again
Jennifer

"Bob Phillips" wrote:

Try

=SUMPRODUCT(--(YEAR(F2:F174)=2006),--(MONTH(F2:F174)=7))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer1960" wrote in message
...
Using =HLOOKUP(999999999999,F2:AC2,1,1) I am obtaining a last date
entered
in
E2.
Must type date in DD/MM/YYYY format.

To add how many time a date (by month appears) Im using
=COUNTIF(F2:F174,"*.7.2006")

It appears to me a conflict of date formats - just not sure how to
change
so
both formulars read. Any Assistance?