excel

[Solved] how to calculate time difference in excel working hours only

by

You will need a helper column with this formula: =24*(SUMPRODUCT((TEXT(ROW(INDEX(AAA:AAA,$F$1):INDEX(AAA:AAA,$F$2)),”dddd”)=A1)*(C1-B1))-IF(TEXT($F$1,”dddd”)=A1,MOD($F$1,1)-B1,0)-IF(TEXT($F$2,”dddd”)=A1,C1-MOD($F$2,1),0)) Then sum that column. Here it is in one formula using NETWORKDAYS.INTL =IF(DATEDIF(F1,F2,”d”)>1,NETWORKDAYS.INTL(F1+1,F2-1,”0000011″)*12+NETWORKDAYS.INTL(F1+1,F2-1,”1111101″)*4,0)+IF(DATEDIF(F1,F2,”d”)>0,(MOD(F2,1)-IF(WEEKDAY(F2,2)<6,TIME(7,0,0),TIME(9,0,0)))*24+(IF(WEEKDAY(F1,2)<6,TIME(19,0,0),TIME(13,0,0))-MOD(F1,1))*24,(F2-F1)*24) 4 solved how to calculate time difference in excel working hours only

[Solved] How can validate data in excel so that we can throw an error if the decimal point of an input is less than “0.12”.ex: need to exclude 1.13

by

How can validate data in excel so that we can throw an error if the decimal point of an input is less than “0.12”.ex: need to exclude 1.13 solved How can validate data in excel so that we can throw an error if the decimal point of an input is less than “0.12”.ex: need to … Read more

[Solved] How to use VBA in Google Sheets to highlight cells with special characters and uppercase letters?

by

I don’t think you need scripts. You could use conditional formatting. It seems the only reason you’re using VBA is because you need REGEX, which Microsoft excel doesn’t support except through VBA. Google Sheets however has REGEX support inbuilt. … highlights all Cells in Range C1 to E10000 which contain anything else than lowercase a–z, … Read more

[Solved] (GetElement)Clicking a button with no ID, TAG, NAME, CLASS, VALUE, SIBLINGS

by

OP here. Sorry for the terrible formatting of the question, it was the first time. To help others in the future….. This is what I did at first. It was my band aid fix. ie.document.getelementByID(“filtervalue”).select “SendKeys{ENTER}” This highlighted my input value and just pressed enter. If you are new to VBA and need something to … Read more

[Solved] Filter date to update pivot table daily [closed]

by

I can’t help myself… Base Data Used: Pivot Table Layout: Code Used: ‘ *** On Workbook Module *** Option Explicit Private Sub Workbook_Open() Call UpdatePivotTableDateToYesterday End Sub ‘ *** In Module 1 *** Option Explicit Sub UpdatePivotTableDateToYesterday() Sheet5.PivotTables(“PivotTable4”).PivotFields(“Date”).ClearAllFilters Sheet5.PivotTables(“PivotTable4”).PivotFields(“Date”).CurrentPage = Format(Date – 1, “m/d/yyyy”) End Sub 2 solved Filter date to update pivot table daily … Read more

[Solved] How to Shift an array circularly on VBA [closed]

by

Option Explicit Option Base 1 Sub shiftCircArray() Dim iInputArray(3) As Integer iInputArray(1) = 1 iInputArray(2) = 2 iInputArray(3) = 3 Dim iArray2() As Integer iArray2 = RotateArrayRight(iInputArray) End Sub Function RotateArrayRight(ArrayToRotate) Dim objNewArray() As Integer, iOldArrayPos As Integer, iNewArrayPos As Integer, iArrayLength As Integer Dim iPlacesToRotate As Integer ‘ Check that the array to be … Read more

[Solved] How to replace comma with space in text file using VBA [closed]

by

This should do the trick: Sub foo() Dim objFSO Const ForReading = 1 Const ForWriting = 2 Dim objTS ‘define a TextStream object Dim strContents As String Dim fileSpec As String fileSpec = “C:\Test.txt” ‘change the path to whatever yours ought to be Set objFSO = CreateObject(“Scripting.FileSystemObject”) Set objTS = objFSO.OpenTextFile(fileSpec, ForReading) Do While Not … Read more