• ## Search first X characters for four characters in a row that match value in a Table

Re: Search first X characters for four characters in a row that match value in a Tabl

The 29 is just a little trick......

I used this part

TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")

to generate this array

{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}

You could replace the first with the latter if you want

it works because

29*{1,2,3,4,5,6,7,8,9,10,11,12} generates this array

{29,58,87,116,145,174,203,232,261,290,319,348}

and as Excel starts counting dates from 1/1/1900 each of those numbers represents a date in successive months in 1900.

I started by just searching for the month but in some of your data you had "aug" or similar somewheree in the other text so I looked for "Aug1" etc. to make sure it was a date......let me have a look and see if I can improve that.......

• ## Search first X characters for four characters in a row that match value in a Table

Re: Search first X characters for four characters in a row that match value in a Tabl

The file you attached was an Excel 2007 file so I assumed you could use IFERROR, or do you not want to?

You have some parentheses out of place with ISERROR, try

=IF(ISERROR(TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5))),"",TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5)))

although, I dislike repeating the whole formula. If there is an error it will be with the SEARCH part so you could shorten that with this version

=IF(COUNT(SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1)),TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5)),"")

• ## Search first X characters for four characters in a row that match value in a Table

Re: Search first X characters for four characters in a row that match value in a Tabl

Try this one

=IFERROR(TRIM(MID(A5,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,A5))-2,7)),"")

Note that like the above this searches for the month followed by a 1 so it wouldn't work for a date like 3Aug09 or 24Dec23......although I could probably amend to suit.........

• ## Extract specific numeric portion of text string

Re: Can I use numeric wildards in a Find and accommodate decimals in what gets return

Try this formula for data in A5

=IFERROR(LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0),"")

• ## Search first X characters for four characters in a row that match value in a Table

Re: Search first X characters for four characters in a row that match value in a Tabl

Try this formula for row 5 copied down

=IFERROR(TRIM(MID(A5,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,A5))-7,5)),"")

• ## Calculate time interval based on start and end date and time

Re: Excel help on DownTime Formula

Assuming your working days are Monday to Friday and that start and end times will always be within the working hours then you could use this formula in C2

=(NETWORKDAYS(A2,B2)-1)*9/24+MOD(B2,1)-MOD(A2,1)

format C2 as [h]:mm

Please advise if working days are different or if start and end can be at evenings weekends etc......

• ## difference between 2 times in yyyy.mm.dd hh:mm

Re: difference between 2 times in yyyy.mm.dd hh:mm

Assuming your date/times are text-formatted then a straight subtraction won't work. Try using SUBSTITUTE function to replace . with - to give a valid date format, i.e. in C2

=SUBSTITUTE(B2,".","-")-SUBSTITUTE(A2,".","-")

custom format C2 as [h]:mm

• ## Add & Multiply Man-hours - Different results

Re: Add &amp; Multiply Man-hours - Different results

You will get different results because you are not doing the same thing. However these two formula will give you the same result

=SUM(D20:D26)

=SUMPRODUCT(C6:C12,C20:C26)

• ## calculation of total working days in different month in different columns

Re: calculation of total working days in different month in different columns

Perhaps try this formula in D3 copied across and down

=MAX(0,NETWORKDAYS(\$C3,EOMONTH(D\$2,0)))

see attached

If that doesn't give the results you want then can you show the required results - thanks

• ## calculation of total working days in different month in different columns

Re: calculation of total working days in different month in different columns

You might still get negatives if the month matches but the year doesn't - I suggest changing formula to

=IF(\$C3-DAY(\$C3)=E\$2-DAY(E\$2),NETWORKDAYS(\$C3,EOMONTH(E\$2,0)),0)

• ## Calculate using data returned from look up of variable inputs

Re: Can INDEX or LOOKUP be used with IF to change the way a number is calculated?

Probably the simplest way to establish whether a date exists in a range is to use COUNTIF, so if you want to check whether a date in C2 exists in B2:B100 of sheet2 you can use a formula like this

=IF(COUNTIF('Sheet2'!\$B\$2:\$B\$100,C2)>0,"Date matches","No match")

• ## Lookup Nth Occurence

Re: Lookup Nth Occurence

Hello HSU,

The specific reason that your formula returns #N/A is that you have an #N/A error in the range 'Risk Register Testing'!AA11:AA85 (in AA34). You either need to prevent that at source (change formulas in 'Risk Register Testing'!AA11:AA85) or alter your other formula to ignore #N/A

In any case your formula won't extract the correct value. The first 6.2 is in row 16 so the SMALL part of the formula returns 16, so this.....

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)),1))

....becomes

=INDEX('Risk Register Testing'!AB11:AB85,16)

the 16th value in the range 'Risk Register Testing'!AB11:AB85 is in 'Risk Register Testing'!AB26, I assume you want the value from 'Risk Register Testing'!AB16. The best way to do that is to change the formula to this

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1),1)

....or to ignore error values

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF(ISNUMBER('Risk Register Testing'!AA11:AA85),IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1)),1))

Note: I think there is a value in referring to the other worksheet within the ROW function. In the formula above if some rows are added or deleted below or within the range the formula will adjust to the new range, hence it's more robust

• ## How to use countif and vlookup together.

Re: How to use countif and vlookup together.

COUNTIF won't work if the source workbook is closed - try using SUMPRODUCT, i.e.

=SUMPRODUCT((LEFT('C:\[report.xls]3O0UZSPJ'!A:A,3)="MX1")+0)

• ## vlookup extract next cell and combine with if

Re: vlookup extract next cell and combine with if

So if T2 matches with B8 you want the value from B9? Try

=IF(U2<5;T2;INDEX(KO'!B:B;MATCH(T2;'KO'!B:B;0)+1))

• ## Count only the unique numbers in a row

Re: Count only the unique numbers in a row

If some of the "numbers" are text formatted, and you want to count those too then try revising Mike's original formula like this

=SUMPRODUCT(ISNUMBER(D4:CK4+0)*(D4:CK4<>"")/COUNTIF(D4:CK4,D4:CK4&""))

One possible side effect is that would count as a number anything that coerces to a number, e.g. 1/1 which becomes a date (number) when you co-erce with +0

• ## SUMPRODUCT function where condition is cell range?

Re: SUMPRODUCT function where condition is cell range?

You can use MATCH to deal with a range as criteria, i.e.

=SUMPRODUCT(('Data Page'!\$B\$8:\$B\$8630=”Something”)*ISNUMBER(MATCH('Data Page'!\$J\$8:\$J\$8630,A2:A3,0)))

That approach means you can easily accommodate a criteria range of 10 or 100 cells (and the range can be either vertical or horizontal, it doesn't matter which as long as it's just a single column or row)

• ## Excel formula for counting with Base 26 letters- like an alphabetic barcode!

Re: Excel formula for counting with Base 26 letters- like an alphabetic barcode!

Hello Xcellent - perhaps I'm doing something wrong. I put an "A" in A1 and then that formula in B1 and copy down I just get " B" in B1 and nothing in the other cells, do you expect there to be values in A2 down? If I put values in there then I just get repeats of those in column B......

• ## Working out difference between two dates and times but minus weekends

Re: Working out difference between to dates and times but minus weekends

Assuming that the dates aren't at the weekend you could use NETWORKDAYS like this to exclude weekends, no need to list them

=NETWORKDAYS(B3,C3)-1-(MOD(C3,1)<MOD(B3,1))&" days"&TEXT(C3-B3,""" and"" h ""hours and ""mm"" minutes""")

• ## Find dates within date range

Re: Find dates within date range

Small change to PCI's formula will check whether any of the three dates fall in the range

=(B7<=EndDate)*(B7>=StartDate)+(C7<=EndDate)*(C7>=StartDate)+(G7<=EndDate)*(G7>=StartDate)=1

• ## Calculation of dates and funding between two fiscal years

Re: Calculation of dates and funding between two fiscal years

Hello Tessy,

I'm a little confused by this - rather than quoting formulas that don't work I think it would be better for you to explain what you want to do exactly.

What results do you expect to get in K5:L6 for instance - are the figures shown there the results you want or should they be different? Don't forget there aren't exactly 52 weeks in any year....so 253.85 per week isn't the same as 13,200 a year...and some years are longer than other years....