Creating Calendar Spreadsheets with PowerShell

by ps1Feb 7, 2022

Ever needed to plan recurring meetings for your club, community or hobby? Sure, there are tons of online tools to help you but if you’d just like to create a calendar list in Microsoft Excel, PowerShell can be an excellent helper.

Let’s assume you have a recurring meeting every Wednesday, and the meeting starts at 12PM except in the last week of each month.

Rather than adding these dates and times manually to an Excel sheet, you may want to use PowerShell like this:

# generate calendar for weekly incidents

$startdate = [DateTime]'2022-06-01'
$numberOfWeeks = 52
$result = for ($week = 0 $week -lt $numberOfWeeks $week ++) 
{
  # calculate the real date each week
  $realdate = $startdate + (New-Timespan -days (7*$week))
  
  # calculate the current month
  $month = $realdate.Month

  # calculate the days in this month
  $daysInMonth = [DateTime]::DaysInMonth($realdate.Year, $realdate.Month)

  # make arbitrary adjustments, i.e. set start time to 12PM by default, but 7PM on the last week of a month

  # are we in the last week of a month?
  if ($realdate.Day -gt ($daysInMonth-7))
  {
    # add 19 hours
    $realdate = $realdate.AddHours(19)
  }
  else
  {
    # add 12 hours
    $realdate = $realdate.AddHours(12)
  }
  
  # create your Excel sheet layout as a CSV file
  [PSCustomObject]@{
    Start = $realdate
    IsOnline = $false
    Title = ''
    Speaker = ''
    Notes = ''
  }
} 


$path = "$env:temp\calendar.csv"
$result | Export-Csv -UseCulture -Path $path -Encoding UTF8 -NoTypeInformation 

# open CSV in Excel
Start-Process -FilePath excel -ArgumentList $path

This script illustrates a number of useful techniques:

Constructing dates in a loop with offsets (7 days in this example, could be easily adjusted to any other interval)
Identifying "last week in month" by calculating the days in a current month, then making adjustments on a date based on this
Generating CSV data and opening CSV in Microsoft Excel (if installed)

ReTweet this Tip!

Creating Calendar Spreadsheets with PowerShell

Tags