Search results

@ Joeman187 So what about if they have a lunch break longer then 30 minutes!!

May be! (StartTime - Lunchstart) + (LunchEnd - EndTime)

Hi Ananthram Syntax : CHOOSE(index_num, value1, [value2], ...) For example: In A1 a number 1 to 7 (index_num), Value1= "Mon",Value2 ="Tue" etc to Value7 ="Sun". Have a look here for more detail on the CHOOSE function...

"Then how do I count such occurrences in the data with certain criteria. Countifs?" Read books comes to mind!

Hi prazad82 You cant use wildcard with SUMPRODUCT. Try: =SUMPRODUCT(--(ISNUMBER(SEARCH("Tech",Audits!$B$2:$B$179,1)))*(Audits!$A$2:$A$179>=StartDate)*(Audits!$A$2:$A$179<=EndDate)*(Audits!$F$2:$F$179=Plan!ProductCategory))

Hi tafatafa Use the INDEX & MATCH functions.

Hi Find & Replace Or a function, assuming your data is in A1:A3, in B1 & copy down: =SUBSTITUTE(A1,","," ") Kevin

Hi INDEX & MATCH! =INDEX('Period Dates'!$A$3:$A$15,MATCH(A3,'Period Dates'!$B$3:$B$15,1))

@ vijay.vizzu If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Hi Assuming your data is in A1:B6 including your column headers. In D1 = a & E1 = b. Then in A2: =IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=D$1,ROW($A$1:$A$6)-ROW($A$1)+1,""),ROW(A1))),"") This is an array formula: CTRL + SHIFT + ENTER Copy across to B2 and down.

Hi rsk Format the cell/cells as: [h]:mm