Category: MsOffice

Calculate the number of workdays using Excel

by ucinfo, posted in MsOffice

This is a cool function, that I didn’t know about.  I basically wanted to find out the number of working days between two dates.  Found the NETWORKDAYS function! This is extracted from the Help file ;-)

Description
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Tip: To calculate whole workdays between two dates by using parameters to indicate which and how many days are weekend days, use the NETWORKDAYS.INTL function.

Syntax
NETWORKDAYS(start_date, end_date, [holidays])

The NETWORKDAYS function syntax has the following arguments:
Start_date    Required. A date that represents the start date.
End_date    Required. A date that represents the end date.
Holidays    Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

Powershell: Import CSV files to Excel

by ucinfo, posted in MsOffice, Powershell

#Powershell

So Ari asked if I could knock up a script that would import csv files in to Excel .. and here it is.  It uses the Excel object model, so excel and powershell need to be on the box you this from

Basically copy the csv files you want to your “rootfolder” of choice and run the script.  It uses the filename for the tab name.

$RootFolder = “C:temp”
$filename = “$RootFolderExcelSpreadsheet.xls”
$files = dir -Path $RootFolder *.csv

$excel = new-object -comobject Excel.Application
$excel.visible = $true
$workbook = $excel.workbooks.add()
$sheets = $workbook.sheets
$sheetCount = $Sheets.Count
$mySheet = 1
$mySheetName = “Sheet” + $mySheet
$s1 = $sheets | where {$_.name -eq $mySheetName }
$s1.Activate()

If($sheetCount -gt 1){
#Delete other Sheets
$Sheets | ForEach{
$tmpSheetName = $_.Name
$tmpSheet = $_
If($tmpSheetName -ne “Sheet1”){$tmpSheet.Delete()}
}
}

ForEach($file in $files){
If($mySheet -gt 1){$s1 = $workbook.sheets.add()}
$s1.Name = $file.BaseName
$s1.Activate()
$s1Data = Import-Csv $file.FullName
$s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip
$s1.cells.item(1,1).Select()
$s1.Paste()
$mySheet ++
}

$workbook.SaveAs($FileName)
$excel.Quit()

 

Let me know what you think

Microsoft Announces Office 365

by ucinfo, posted in !News, Flaphead, MsOffice

#office #exchange #office365

Interesting …

REDMOND, Wash. — Oct. 19, 2010 — Microsoft Corp. today announced Microsoft Office 365, the company’s next generation in cloud productivity that brings together Microsoft Office, SharePoint Online, Exchange Online and Lync Online in an always-up-to-date cloud service. Office 365 makes it easier for millions more organizations to get and use Microsoft’s award-winning business productivity solutions via the cloud. With Office 365, people can work together more easily from anywhere on virtually any device, while collaborating with others inside and outside their organization in a simple and highly secure way. As part of today’s news, Microsoft is also opening a limited beta program for Office 365 in 13 countries and regions.

…

Today at 12 p.m. PDT, Microsoft will launch http://www.Office365.com. Customers and partners can sign up for the Office 365 beta and learn more at that site, or follow Office 365 on Twitter (@Office365), Facebook (Office 365), or the new Office 365 blog at http://community.office365.com to get the latest information.

Microsoft Office 2010 Beta Coming Soon – Technical Preview Program to Close

by ucinfo, posted in Flaphead, MsOffice

Got an email on Friday .. about time too the Tech Preview is a bit buggy, but you expect that

Thank you for continuing to utilize, evaluate and provide feedback on the Microsoft Office 2010 Technical Preview.

We wanted to notify you the Office 2010 Beta will be available for you to download next month (November 2009).

The Beta release of Office 2010 marks the end of the Technical Preview program you currently belong to. We will release the Beta on public download sites, where you can download and install a newer build of Office 2010 client software. At that time, you will also get your first look at the exciting new features we have added to server products such as SharePoint.