Posts by daddylonglegs

    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


    to generate this array


    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


    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.......

    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


    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


    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


    format C2 as [h]:mm

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

    Re: difference between 2 times in 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


    custom format C2 as [h]:mm

    Re: Lookup Nth Occurence

    Hello HSU,

    I replied to your thread at Excelforum, this is my reply.....

    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))


    =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

    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


    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

    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)

    Re: Find dates within date range

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


    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 253.85 per week isn't the same as 13,200 a year...and some years are longer than other years....