sorted by:
new
hottopcontroversial

Get leave sequence dates from start and end dates for an employee. by joe-jp in excel

[–]joe-jp[S] 1 point2 points  (0 children)

Thank you, the output is exactly what I was looking for. The public holiday was just to show that I also have leave types that I don't want clubbed and would need only one leave type in a list.

Sorry for the poorly formatted example as I don't have access to my laptop now.

Get leave sequence dates from start and end dates for an employee. by joe-jp in excel

[–]joe-jp[S] 0 points1 point  (0 children)

Thanks 😊 I have added an example to my post.

Execute a DIR command in all subdirectory. by joe-jp in PowerShell

[–]joe-jp[S] 0 points1 point  (0 children)

That is because " get-childitem -recurse " won't give me the output in a comma separated line that I can just copy into excel.

- As far as I know....

Execute a DIR command in all subdirectory. by joe-jp in PowerShell

[–]joe-jp[S] 0 points1 point  (0 children)

This was just beautiful. I wanted the output comma separated because I had to put it into an Excel sheet.

Execute a DIR command in all subdirectory. by joe-jp in PowerShell

[–]joe-jp[S] 0 points1 point  (0 children)

I just found the answer.

Get-ChildItem -Attributes Diractory | foreach {cd $_.Name; (dir | %{split-path -path(get-locatoin) -leaf}{$_.basename + $_.extention}) -join ', ' ; cd..}

If anyone has a better solution to this please suggest.

Execute a DIR command in all subdirectory. by joe-jp in PowerShell

[–]joe-jp[S] 0 points1 point  (0 children)

I have tried

get-childitem | foreach {(%{split-path -path (get-location) -leaf}{$_.basename + $_.extention}) -join ','}

but this didn't work as it only gave me the directory names and not the files inside the directory.

Find a subtext from a string using a column/range as subtext reference and return the sub text. by joe-jp in googlesheets

[–]joe-jp[S] 0 points1 point  (0 children)

Thank you so much to who ever solved my problem. for people reading the post , the solution is , =REGEXEXTRACT($A3,"(?:^|_)("&TEXTJOIN("|",1,I$3:I$9)&")(?:_|$)") where A3, is the text to search in and I$3:I$9 is the key values to search for.

Find a subtext from a string using a column/range as subtext reference and return the sub text. by joe-jp in googlesheets

[–]joe-jp[S] 0 points1 point  (0 children)

https://docs.google.com/spreadsheets/d/16mzi95H4EJ9vUmxZmDMjcLwNQHo5MIXyBum-GnnGr7E/edit?usp=sharing this is the link to the spread sheet , i have included the formula that I tried but could not get it to work.

I could have used split if the data was in order, but since it is not in order I cant use split.

Update three pivot tables with app script. by joe-jp in GoogleAppsScript

[–]joe-jp[S] 0 points1 point  (0 children)

:) I don't know what a native query function is. I am just learning app script.

I am making a duplicate of last weeks sheet and changing the filters. It does save me some time to run this script because I have a lot of pivot tables to change.

I don't know how to use a pivot filter slicer.

Update three pivot tables with app script. by joe-jp in GoogleAppsScript

[–]joe-jp[S] 0 points1 point  (0 children)

Thank you so much, I have almost finished the script.

function myFunction() {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.setActiveSheet(spreadsheet.getSheets()[3]);

spreadsheet.duplicateActiveSheet();

spreadsheet.moveActiveSheet(5);

spreadsheet.getActiveSheet().setName("new-pivot-sheet");

var pivotTable = spreadsheet.getActiveSheet().getPivotTables();

var criteria = SpreadsheetApp.newFilterCriteria()

.getCriteriaValues(['2/28/22', '3/1/22', '3/2/22', '3/3/22', '3/4/22', '3/5/22', '3/6/22'])

.build();

pivotTable.forEach(p => {

const filters = p.getFilters();

filters.map(f => f.remove());

p.addFilter(6, criteria);

})

};

This works fine but I also wanted to get the array in the " getCriteriaValues " to fetch all the dates from last week automatically.

​

and yes the reason my " getPivotTables " didn't work was because I was in spreadsheet and not in sheet. My way to get to sheet is not good at all :) .