## [Solved] Calucating the total number of cells in a row that are less than 16 hours [closed]

Assuming that column A of sheet1 are date/times, and not text, and you are attempting to find how many date/times are more than 16 hours old then you simply want to place the following formula in sheet2!F7: =COUNTIF(sheet1!A:A,”<“&NOW()-0.75) (16 hours is 0.75 of a day.) If the data in column A is text, then it … Read more

## [Solved] Algorithm needed for product aging in a storage [closed]

This problem is indeed not vba specific. For each box, add a label when it was added to the storage. You may take the point in time as a timestamp, iteration number or whatever unit suits you. Then you can now evaluate the time the box spent in storage by calculating now-time_when_box_was_added, for every single … Read more

## [Solved] Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it

Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it solved Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it

## [Solved] How to define a range of values while writing IF condition in Excel?

Try this =IF(A1<25,”Young”,IF(AND(A1>=25,A1<=65),”Middle Aged”,”Old”)) 0 solved How to define a range of values while writing IF condition in Excel?

## [Solved] Repeating series of numbers-unique id needed

Doable with formulas and and helper column: In column E (Helper): =IF(A2=A1,””,A2) In Column C (Output): =IF(E2=””,C1,CHAR(COUNTIF(\$E\$2:E2,E2)+64)) 0 solved Repeating series of numbers-unique id needed

## [Solved] Sequence of numbers based on start and end value of 2 cells [closed]

You can use these formulas D13: =Q7 E13: =IFERROR(IF(D13+1>\$S\$7,””,D13+1),””) Then copy E13 to the right as far as you need. 1 solved Sequence of numbers based on start and end value of 2 cells [closed]

## [Solved] How to convert to percentage [closed]

Perhaps the following VBA solution using Regular Expressions. Function FixMyPercentages(target As String) As String Dim output As String output = target With New RegExp .Global = False .MultiLine = True .IgnoreCase = False .pattern = “\s\d+\.\d+\$|^\d+\.\d+\s|\s\d+\.\d+\s” Dim myMatch As Object, myMatches As Object Do While .test(output) Set myMatches = .Execute(output) For Each myMatch In myMatches … Read more

## [Solved] Calculating partial months salary costings for employees on a project in Excel [closed]

In cell I2, put the following formula (see UPDATE section for a simplified version): =LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1), startsPrj, INDEX(setPrj,,4), endsPrj, INDEX(setPrj,,5),names, INDEX(setRoster,,1),starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6), empty, “,,”, SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(“;”,,x),”,”,”;”), z, FILTER(y, INDEX(y,,1)<>””, NA()), IFS(case=0, z,case=1, HSTACK(INDEX(z,,1), 1*CHOOSECOLS(z,2,3)), case=2, 1*z))), BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ends, … Read more

## [Solved] Prepare Macros which add comma after every 10 digit in a column [closed]

Please try this, it should work for you, Sub AddCommas() Dim s As String Dim i As Long Dim j As Long For k = 1 To 20 For i = 1 To Rows.Count s = “” If Not IsEmpty(Cells(i, k).Value) And Int(Len(Cells(i, k)) / 10) >= 1 Then For j = 1 To Int(Len(Cells(i, … Read more

## [Solved] I need to be able to match a date and store number to an array that has the store numbers, a date range and return the PO Number on that line

If the search date is in G1… If the search store is in G2… If the data are in columns A, B, C, and D… You can use this formula to return the first PO# where the search date is between the Start and End dates and where the search store matches Store #: =INDEX(D:D,MATCH(1,(A1:A999<=G1)*(B1:B999>=G1)*(C1:C999=G2),)) … Read more

## [Solved] How To Automatically Generate Invoice Number In Excel? [closed]

If you are willing to be limited to a maximum of three letters for the company name, you can use a formula: =CONCATENATE(LEFT(B3,1),MID(B3,FIND(” “,B3)+1,1),IFERROR(MID(B3,FIND(” “,B3,FIND(” “,B3)+1)+1,1),””),TEXT(H3,”DDMMYY”)) 1 solved How To Automatically Generate Invoice Number In Excel? [closed]

## [Solved] What is the excel formula to find the upcoming Saturday a month?

=EOMONTH(TODAY(),0)+7-WEEKDAY(EOMONTH(TODAY(),0)+7) If you want it based on the current date, find the next first Saturday: =TODAY()+7-WEEKDAY(TODAY()+7) This will find the next first saturday of the month. So on the 6th of this month it will return 2/9/2017 3 solved What is the excel formula to find the upcoming Saturday a month?

## [Solved] Extracting the characters between two – in the current string in an excel macro [closed]

In your case, if the suggested method Text-To-Columns is not an option somehow, you could use: =TRIM(MID(SUBSTITUTE(A1,”-“,REPT(” “,LEN(A1))),2*LEN(A1)+1,LEN(A1))) This part 2*.. stands for (N-1)*.., in this case the third ‘word’ More information here solved Extracting the characters between two – in the current string in an excel macro [closed]

## [Solved] Round cells then auto fill VBA

This should help you get started. Range.Formula property on MSDN Range.AutoFill method on MSDN With Activesheet.Range(“AR8”) .Formula = “=ROUND(IF(AP8>(AN8*P8),AP8,AN8*P8),2)” .AutoFill Destination:=.Resize(100), Type:=xlFillDefault End With 0 solved Round cells then auto fill VBA

## [Solved] EXCEL – Search formula with multiple criteria

The following array formula returns the first entry in column B where it is not null and also where column A has cell value aaaaa. = IFERROR(INDEX(B1:B6,MATCH(1,(A1:A6=”aaaaa”)*(B1:B6<>”null”),0)),”no match”) Note this is an array formula, so you must press Ctrl+Shift+Enter on the keyboard after typing the formula rather than just pressing Enter. To return a similar … Read more