• ## Named Ranges

"Issue is using data validation feature against a named range not on the same tab."

If the range is named, it shouldn't be a problem. Specifically, what's going wrong when you try it - describe the step up steps you're using in detail.

Hi noob.

What have you got so far??

• ## Date and Time problem

datedif() is in 97, it's just not documented. the following is from the 2000 help file:

Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.

Syntax

DATEDIF(start_date,end_date,unit)

Start_date is a date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")). For more information about date serial numbers, see NOW.

End_date is a date that represents the last, or ending, date of the period.

Unit is the type of information you want returned.

Unit Returns
"Y" The number of complete years in the period.
"M" The number of complete months in the period.
"D" The number of days in the period.
"MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored.
"YD" The difference between the days of start_date and end_date. The years of the dates are ignored.

• ## date validation

Neale,

the board has a habit of interpreting < as the start of an html tag. add a space between it & the next item in the formula to get the whole thing displayed:

=AND(WEEKDAY(C7)=2,C7&gt;(NOW()-14),C7< NOW())

• ## [Solved] formulae that can be used to calculate YTD

although note that offset is volatile, so usng it a lot can reduce the file performance. index() can often be used instead. eg, an index equivalent for Neale's

=SUM(B7:OFFSET(B7,0,E1-1))

would be:

=SUM(B7:INDEX(B7:M7,1,E1-1))

• ## Date and Time problem

you could also consider datedif():

the formula is:

=DATEDIF(A2,TODAY(),"M")

• ## COUNTING A CELL IF ANOTHER CELL =

given the data layout in the file posted by thomach, a "one shot" formula equivalent is:

=SUMPRODUCT((A1:A11=D1)*(B1:B11=D3))

...you could also consider a dcounta() alternative

• ## COUNTING A CELL IF ANOTHER CELL =

If both elements are text entries, why not just use a pivot table?

• ## COUNTING A CELL IF ANOTHER CELL =

When you say "B1 is the time of day e.g AM or PM. " do you mean that there's a genuine time in there, or just a text "AM" or "PM"??

• ## Multiple conditions within sumproduct?? [SOLVED]

multiple conditions can be accomodated in sumproduct() formulas as follows:

=SUMPRODUCT((A16:A18={1,2})*(B16:B18="a")*(C16:C18))

• ## Changing AM to PM "SOLVED"

If it's an "excel time", then it should be stored as a time value already.

one option - in a spare cell, put 0.5 (=half a day = 12 hours). select the range of cells of interest, go to paste special, select add.

the results of an equivalent operation using formulas are given below:

• ## Need to modify formula [SOLVED]

"and I don't know why the formula would not post correctly"

...because the board software has a habit of interpretting greater than & less than signs as html tags. one option would be to turn off html for the post, but it can't find it on hte preferences etc. another might be to leave a space between a less than sign & the next bit of the formula -the following test to see if this works:

IF(F8 < N8,"LESS THAN BUDGET",IF(F8 &gt; N8,"MORE THAN BUDGET","AT BUDGET"))

EDIT: which it does!

• ## [Solved] Autofilter

or, for a formula option:

=SUBTOTAL(3,A:A)-1

where the 3 = counta and the -1 is to subtract the instance of a column header...(change the 3 to a 2 if you're counting numerics).

• ## Excel formula/data query [SOLVED]

Hi - welcome to the board!

see the example:

th formulas are of the form:

=IF(LEN(A2),A2&" "&B2&" "&TEXT(C2,"hh:mm"),"")

• ## Using LIKE in an IF statement? [SOLVED]

=IF(LEN(A2)=2,"0000"&A2,IF(LEN(A2)=4,"00"&A2,A2))

shorter - and more general - option:

=text(a2,"000000")