Conflicking dates in formula

[Jennifer1960]

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)

"Jennifer1960" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Jennifer1960]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >