Search results

Maybe with a formula. The formula uses the helper data range in 'A2:B11', which is named "rng". =IFERROR(VLOOKUP(MID($E2;1;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;2;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;3;1)*1;rng;2;FALSE);"")&"...

Great @bosco_yip. Thank you. My respect.

I opened a new thread. https://chandoo.org/forum/threads/subtract-time.46779/

I am opening a new thread related to this topic https://chandoo.org/forum/threads/set-break-after-certain-time.46753/ This is a situation where I want to subtract hours every 10 minutes but without an helper column? Is that possible? Attached file.

I have an extra question. I still try to avoid auxiliary 'C' and 'F' columns in which 'break' is. So I want to subtract time and reset after 10 minutes. But not to find a way. For example, in the 'B12' cell expected result is 0:11 minutes. Is there a possibility for this solution?

@bosco_yip Thank you for the solution offered. I solved it an hour ago. My long formula...

I want to note, that my formulas do not necessarily have to be used. Any solution that gives the desired result will satisfy my needs.

The last cell in the 'B' column that contains more than 10 minutes is 'B4' (0:16). The last cell in the 'C' column which contains a break is 'C4'. The 'A4' cell contain 5:38 The 'A12' cell contain 5:27 5:38-5:27=0:11

I work on one task with multiple conditions and I solved it around 90%. (I use the Excel 2013) There is one problem left. I can not find a way to subtract time while I copy the formula down and set a break after a certain time. I use this formula below, into 'B' column and everything is fine...

Hi @bosco_yip Thank you for your answer. I know that CHAR(60) returns '<' and CHAR(62) returns '>'. At this moment, I don’t know why I concentrated on the formula separator, so the separator in the formula seemed problematic to me. I have tried many variations and always involved separator. I...

Hi Can anyone help me (i.e. explain) why the formulas below do not work in my Excel 2013? I use Non-US settings on my Windows OS and also in Excel (Eastern Europe). I know that the separator in Excel formulas is different from US settings and Non-US settings. I'm wondering why the formula I...

That should be addressed to Peter

See if it helps you, an idea with a named range and IF function in Data Validation. =IF(B5="yes",yes,no)

If you want, try without helper column Extract the last two words from the sentence, regardless of the length of the sentence. =MID(A3,FIND(" ",A3,FIND("@",SUBSTITUTE(A3," ","@",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-1),1))+1,256) Extract Second Last word from a sentence...

Try in B1 (copy across) =IFERROR("#"&TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A1,"."," "),FIND("|",SUBSTITUTE(SUBSTITUTE($A1,"."," "),"#","|",COLUMNS($B:B)))+1,LEN($A1))," ",REPT(" ",LEN($A1))),LEN($A1))),"")

Try to see this tutorial "Encryption in Excel". Maybe it will help you solve the task or at least indicate this to the direction of solving. There you have the download file on which the example was made.

See if the attached idea suits to you.

Sorry but I don't fully understand you. You wrote the result in 'K4' cell value 3500? Is this the correct result? Shouldn't the result be 3640? Please write the expected results in 'K2', 'K3', 'K4' and 'K5' cells. What is the logic of your calculations?

So you could set up your solution here, that other forum visitors see how has been solved. ;)

Try in the B1 cell =if(a1="Yes";50;"")

I don't have time to delve deeper into the issue but try the following. - Move all tables and data (columns) to one worksheet, example "Triage". - Create the necessary formulas in 'CA:CJ' columns in the 'Triage' sheet. - When everything works as you wish, move certain columns and data from...

Is this psychological protection? ;)

Try in the 'K8' cell (copy down) =IF(UPPER(MID($J$8,ROW(A1),1))="",0,UPPER(MID($J$8,ROW(A1),1))) in the 'L8' cell ARRAY formula (copy down) =IFERROR(INDEX($D$5:$D$13,MATCH(TRUE,ISNUMBER(SEARCH("*"&K8&"*","*"&$E$5:$E$13&"*")),0)),"") in the 'M7' cell =TRIM(CONCATENATE(L8,L9,L10,L11,L12,L13))...

I think that there is no 100% protection of the worksheet.

Thank you @bosco_yip So simple but so complicated, after hours of work my brain was blocking. :)