Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS calc with 3 columns of dates | Excel Worksheet Functions | |||
formula for specific dates based on another date in worksheet | Excel Discussion (Misc queries) | |||
date formula for extracting unique dates | Excel Worksheet Functions | |||
Formula including dates | Excel Worksheet Functions | |||
How do I format dates accessed by a formula | Excel Discussion (Misc queries) |