# Search:

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

Page 1 of 6 1

1. ## Thread: Formula To Randomise Names

by Phuoc
Replies
3
Views
203

### Re: Formula To Randomise Names

Try this:

=RAND()+COUNTIF(\$A\$2:A2,A2)*10000

by Phuoc
Replies
8
Views
160

### Re: Years

Or try:

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

by Phuoc
Replies
4
Views
96

### Re: Return a date based on it being Monday, Thursday or Friday

A little shorter:

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

by Phuoc
Replies
4
Views
96

### Re: Return a date based on it being Monday, Thursday or Friday

Or try this:

=WORKDAY.INTL(A1-1,1,"0110011")+7
5. ## Thread: Convert date to weekday name

by Phuoc
Replies
8
Views
68

### Re: Convert date to weekday name

You're welcome! Glad it worked. :beerchug:
6. ## Thread: Convert date to weekday name

by Phuoc
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. ## Thread: Rounding in CONCATENATE

by Phuoc
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. ## Thread: Stock market 15 minute calendar

by Phuoc
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. ## Thread: Stock market 15 minute calendar

by Phuoc
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. ## Thread: Convert duration in text to HH:MM:SS format in excel

by Phuoc
Replies
9
Views
259

### 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. ## Thread: How to shorten if or formula ?

by Phuoc
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. ## Thread: Conditional formatting is not working for less than TODAY(), only for greater than

by Phuoc
Replies
2
Views
136

### 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. ## Thread: Extracting numbers before a certain word or symbol in a long string of Text.

by Phuoc
Replies
4
Views
137

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

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

by Phuoc
Replies
4
Views
137

### 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. ## Thread: SumIF date is certain day of month to next month day.

by Phuoc
Replies
1
Views
72

### 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. ## Thread: Counting Blank Cells

by Phuoc
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. ## Thread: Check if a cell contains same digits

by Phuoc
Replies
36
Views
807

### Re: Check if a cell contains same digits

MsgBox Range("A1").Text - Left(Range("A1").Text, 1) * 1111 = 123
18. ## Thread: Check if a cell contains same digits

by Phuoc
Replies
36
Views
807

### Re: Check if a cell contains same digits

MsgBox Range("B1").Value Mod 1111 = 0
19. ## Thread: Check if a cell contains same digits

by Phuoc
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. ## Thread: Rank Function without Zeros

by Phuoc
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. ## Thread: Ignoring blanks in a sumproduct formula

by Phuoc
Replies
4
Views
261

### Re: Ignoring blanks in a sumproduct formula

Try:

=SUMPRODUCT(--(C40:C53=C6),AW40:AW53,G40:G53)

by Phuoc
Replies
12
Views
482

### Re: String Function

Should be:
For X = 1 To InStr(S, "/") - 1
23. ## Thread: Convert text date to start and end date

by Phuoc
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. ## Thread: Issue with rank formula

by Phuoc
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. ## Thread: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

by Phuoc
Replies
9
Views
182

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