Conflicking dates in formula

[Guest]

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?

 

[Bob Phillips]

Try

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

--
HTH

Bob Phillips

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

 

[Bob Phillips]

Try

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

--
HTH

Bob Phillips

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

 

[Guest]

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

 

[Guest]

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]

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)

 

[Bob Phillips]

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)