Search:

Type: Posts; User: Phuoc; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.01 seconds.

  1. Replies
    3
    Views
    203

    Re: Formula To Randomise Names

    Try this:

    =RAND()+COUNTIF($A$2:A2,A2)*10000
  2. Thread: Years

    by Phuoc
    Replies
    8
    Views
    160

    Re: Years

    Or try:

    =SUBSTITUTE(TRIM(TEXT(A2,"yyyy;;;")&" "&TEXT(B2,"yyyy;;;"))," ","/")
  3. Re: Return a date based on it being Monday, Thursday or Friday

    A little shorter:

    =WORKDAY.INTL(A1+6,1,"0110011")
  4. Re: Return a date based on it being Monday, Thursday or Friday

    Or try this:

    =WORKDAY.INTL(A1-1,1,"0110011")+7
  5. Replies
    8
    Views
    68

    Re: Convert date to weekday name

    You're welcome! Glad it worked. :beerchug:
  6. Replies
    8
    Views
    68

    Re: Convert date to weekday name

    Try this:

    =TEXT(DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)),"ddd")
  7. Replies
    4
    Views
    74

    Re: Rounding in CONCATENATE

    Or try:

    =CONCATENATE(VLOOKUP(G3;A1:E24;4;0);" / ";VLOOKUP(G3;A1:E24;5;0);" = ";
    TEXT(VLOOKUP(G3;A1:E24;4;0)/VLOOKUP(G3;A1:E24;5;0);"0.00%")
  8. Replies
    4
    Views
    144

    Re: Stock market 15 minute calendar

    Sorry, try this:

    =IF(ROUND(MOD(A1,1)*1440,0)= 945,WORKDAY(A1,1,[holidays])+570/1440,A1+15/1440)
  9. Replies
    4
    Views
    144

    Re: Stock market 15 minute calendar

    9/3/19 9:30 AM in A1, try this the formula in A2 and copy down:

    =IF(ROUND(MOD(A1,1),0)*1440= 945,WORKDAY(A1,1,[holidays])+570/1440,A1+15/1440)
  10. Re: Convert duration in text to HH:MM:SS format in excel

    One way:

    =IFERROR(LEFT(A1,SEARCH("hour",A1)-1)/24,0)
    +IFERROR(MID(0&A1,SEARCH("Minute",0&A1)-3,2)/1440,0)
    +IFERROR(MID(0&A1,SEARCH("Second",0&A1)-3,2)/86400,0)
  11. Replies
    8
    Views
    262

    Re: How to shorten if or formula ?

    Try this:

    =IF(OR(MIN(B11:B21)<$I$16,MAX(B11:B21)>$I$19),1,0)
  12. Re: Conditional formatting is not working for less than TODAY(), only for greater than

    Change to:

    1. cell values +0 >= TODAY() are green

    2. cell values + 0< TODAY() are red

    3. cell values = "Not Registered" are yellow

    Or:
  13. Re: Extracting numbers before a certain word or symbol in a long string of Text.

    Haha, you're right. :beerchug:
  14. Re: Extracting numbers before a certain word or symbol in a long string of Text.

    Or try:

    =-LOOKUP(1,-MID(A2,SEARCH(" days",A2)-{1,2,3,4,5,6},{1,2,3,4,5,6}))
  15. Re: SumIF date is certain day of month to next month day.

    Try:

    =SUMIFS($C$14:$C$1000,
    $A$14:$A$1000,">="&WORKDAY.INTL(DATE($A$1,8,0),4,"1111110"),
    $A$14:$A$1000,"<"&WORKDAY.INTL(DATE($A$1,9,0),4,"1111110"),
    $F$14:$F$1000,$AA1)
  16. Replies
    5
    Views
    191

    Re: Counting Blank Cells

    Try this:

    =SUM(INT(FREQUENCY(COLUMN($B3:$JQ$4),IF($B3:$JQ$4<>"",COLUMN($B3:$JQ$4)))/42))

    Array formula, enter with Ctrl+Shift+Enter.
  17. Replies
    36
    Views
    807

    Re: Check if a cell contains same digits

    MsgBox Range("A1").Text - Left(Range("A1").Text, 1) * 1111 = 123
  18. Replies
    36
    Views
    807

    Re: Check if a cell contains same digits

    MsgBox Range("B1").Value Mod 1111 = 0
  19. Replies
    36
    Views
    807

    Re: Check if a cell contains same digits

    MsgBox Range("A1").Text = String(2, Left(Range("A1").Text, 1)) & String(2, Right(Range("A1").Text, 1))
  20. Replies
    7
    Views
    131

    Re: Rank Function without Zeros

    Or use the countifs function ():

    =IF(Q6=0,"",COUNTIFS($Q$6:$Q$20,"<>0",$Q$6:$Q$20,"<"&Q6)+1)
  21. Replies
    4
    Views
    261

    Re: Ignoring blanks in a sumproduct formula

    Try:

    =SUMPRODUCT(--(C40:C53=C6),AW40:AW53,G40:G53)
  22. Thread: String Function

    by Phuoc
    Replies
    12
    Views
    482

    Re: String Function

    Should be:
    For X = 1 To InStr(S, "/") - 1
  23. Replies
    2
    Views
    134

    Re: Convert text date to start and end date

    Start date:

    =--(LEFT(A1,FIND("-",A1)-2)&", "&RIGHT(A1,4))

    End date:

    =--IF(ISNUMBER(--MID(A1,FIND("-",A1)+2,1)),REPLACE(A1,FIND("-",A1)-4,4,""),REPLACE(A1,1,FIND("-",A1)+1,""))
  24. Replies
    11
    Views
    518

    Re: Issue with rank formula

    Try this in C2:

    =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,
    SUM(IFERROR(1/COUNTIFS($A$2:$A$14,$A$2:$A$14,$B$2:$B$14,">"&B2),0))+SUM(IFERROR(1/COUNTIFS($A$2:A2,$A$2:A2,$B$2:B2,B2),0)),...
  25. re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

    Another way:

    =COUNT(SEARCH({", Wrexham,",", Chester,",", Blank,",", Blank,",", Blank,"},", "&I2&","))

    Array formula, enter with Ctrl+Shift+Enter.
Results 1 to 25 of 130
Page 1 of 6 1 2 3 4