Conflicking dates in formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jennifer1960 - 21 Aug 2006 05:44 GMT

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?

Reply to this Message

Bob Phillips - 21 Aug 2006 09:36 GMT

Try

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

Signature

HTH

Bob Phillips

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

> Using =HLOOKUP(999999999999,F2:AC2,1,1) I am obtaining a last date entered in
> E2.
[quoted text clipped - 5 lines]
> It appears to me a conflict of date formats - just not sure how to change so
> both formulars read. Any Assistance?

Reply to this Message

Jennifer1960 - 21 Aug 2006 22:12 GMT

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

> Try
>
[quoted text clipped - 11 lines]
> so
> > both formulars read. Any Assistance?

Reply to this Message

Bob Phillips - 21 Aug 2006 23:56 GMT

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.

Signature

HTH

Bob Phillips

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

> Thanks Bob that worked.
>
[quoted text clipped - 25 lines]
> > so
> > > both formulars read. Any Assistance?

Reply to this Message