Background
A user recently asked how they can make a timesheet with PowerShell. They wanted to be able to specify the hours each day and the days that are included. I figured that PowerShell is a PERFECT solution for that request.
Quick and Dirty Proof of Concept
Looking at the basic code that they were tinkering with, I created this very barebones PoC:
$date =Get-Date
$date = $date.Date
$monday = $date.AddDays(-$date.DayOfWeek + 1)
0..1 | %{ # Days of the week
foreach ($day in $monday.AddDays($_)) {
8..11 | %{ # Hours of the day
[PSCustomObject][Ordered]@{
Date = $day
Start = $day.AddHours($_).TimeOfDay
End = $day.AddHours($_+1).TimeOfDay
}
}
}
}
I just wanted to get something out there and see how it looked. Seemed pretty good, but since this code will be reused week after week, it is better to move it into a function that can be accessed easily.
Note: Did you catch what looks really bad with this PoC? That Foreach-Object and then foreach() are problematic. I address it much further down once I finally noticed it :D
Moving Towards Functions
Sometimes new PowerShell users will ask what’s so special about a function. There isn’t really anything TOO special about them, honestly. They are just generally more flexible and less work than maintaining an archive of useful snippets that you have to paste and adapt on the command line all the time.
The big benefit of a function (or a script) is the use a parameters to specify runtime data depending on what you are wanting for the output. A simple function or script that uses parameters (heck, even ones that don’t) should also make use of the cmdlet binding attribute which allows for PowerShell engine to unlock some of the extra power. Like so:
function New-Thing {
[cmdletbinding()]
param(
# Parameters go here
)
# Code goes here
}
With that in mind, here is my first draft of the “function-ized” version for creating a timesheet
function New-Timesheet {
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[int]$HoursCount = 8,
[int]$StartOfHours = 8,
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1)
)
$StartOfWeek = $StartOfWeek.Date
# Days of the week
0..($DayCount - 1) | ForEach-Object {
foreach ($day in $StartOfWeek.AddDays($_)) {
# Hours of the day
$StartOfHours..($StartOfHours + $Hours - 1) | ForEach-Object {
[PSCustomObject][Ordered]@{
Date = $day.ToShortDateString()
Start = $day.AddHours($_).TimeOfDay
End = $day.AddHours($_ + 1).TimeOfDay
}
}
}
}
}
Sample:
PS> New-Timesheet -DaysCount 5 -HoursCount 1 -StartOfHours 10
Date Start End
---- ----- ---
2/20/2023 10:00:00 11:00:00
2/21/2023 10:00:00 11:00:00
2/22/2023 10:00:00 11:00:00
2/23/2023 10:00:00 11:00:00
2/24/2023 10:00:00 11:00:00
This didn’t output anything to excel for you to fill out as you go through the week. And it didn’t create several hours for the week. But are easy enough for you to do without changing the function at all:
PS> New-Timesheet -DaysCount 5 -HoursCount 4 -StartOfHours 10 | Export-Csv timesheet.csv
Making New-Timesheet More Flexible
If you are someone who needs to create timesheets, you likely already are pointing out things that don’t work for you. Stuff like:
- I want to generate a single timesheet that covers multiple weeks, but skips weekends
- I want to do different intervals besides hourly
Great, lets make it more generic!
Making functions more generic makes them much more usable later on, but it is also generally one of the hardest things to do. There is a balance between making a function too generic (costs a lot of time to create and may cause a lot of boilerplate work to be done for each typical invocation) and too specific (not reusable).
Changing the Interval
For this function, let’s first focus on switching the intervals from “hourly” to “any interval” (but defaulting to hourly):
function New-Timesheet {
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[datetime]$StartOfHours = "8:00AM",
[datetime]$EndOfHours = "5:00PM",
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1), # Monday
[timespan]$Interval = "01:00" # One hour intervals
)
# Set the start of week to midnight to make the StartOfHours addition clean
$StartOfWeek = $StartOfWeek.Date
[timespan]$startOffset = $StartOfHours - $StartOfHours.date
[timespan]$endOffset = $EndOfHours - $EndOfHours.date
# Days of the week
0..($DaysCount - 1) | ForEach-Object {
foreach ($day in $StartOfWeek.AddDays($_)) {
$time = $StartOfWeek.Add($startOffset)
do {
[PSCustomObject][Ordered]@{
Date = $time.ToShortDateString()
Start = $time.TimeOfDay
End = $time.Add($Interval).TimeOfDay
}
$time = $time.add($Interval)
} while ($time -lt $time.Date.Add($endOffset))
}
}
}
Now that we’ve done that, lets see some output!
PS> New-TimeSheet -DaysCount 1 -EndOfHours 12:00
Date Start End
---- ----- ---
2/20/2023 08:00:00 09:00:00
2/20/2023 09:00:00 10:00:00
2/20/2023 10:00:00 11:00:00
2/20/2023 11:00:00 12:00:00
Looking pretty good! And since -EndOfHours is a datetime object, we can use 14:00 to mean 2pm. Or 2:00pm. Or even 2pm.
PS> New-TimeSheet -DaysCount 1 -EndOfHours 2pm
Date Start End
---- ----- ---
2/20/2023 08:00:00 09:00:00
2/20/2023 09:00:00 10:00:00
2/20/2023 10:00:00 11:00:00
2/20/2023 11:00:00 12:00:00
2/20/2023 12:00:00 13:00:00
2/20/2023 13:00:00 14:00:00
Lets also check the interval and see if its working too:
PS> New-TimeSheet -DaysCount 1 -EndOfHours 10am -Interval 0:30
Date Start End
---- ----- ---
2/20/2023 08:00:00 08:30:00
2/20/2023 08:30:00 09:00:00
2/20/2023 09:00:00 09:30:00
2/20/2023 09:30:00 10:00:00
Oh that’s just perfect. And for wonky stuff like every 6 minutes (10ths of an hour), lets see how that would look:
PS> New-TimeSheet -DaysCount 1 -EndOfHours 8:20am -Interval 0:06
Date Start End
---- ----- ---
2/20/2023 08:00:00 08:06:00
2/20/2023 08:06:00 08:12:00
2/20/2023 08:12:00 08:18:00
2/20/2023 08:18:00 08:24:00
Nice. And lets prove that against multiple days:
PS> New-TimeSheet -DaysCount 2 -EndOfHours 8:20am -Interval 0:06
Date Start End
---- ----- ---
2/20/2023 08:00:00 08:06:00
2/20/2023 08:06:00 08:12:00
2/20/2023 08:12:00 08:18:00
2/20/2023 08:18:00 08:24:00
2/20/2023 08:00:00 08:06:00
2/20/2023 08:06:00 08:12:00
2/20/2023 08:12:00 08:18:00
2/20/2023 08:18:00 08:24:00
Ruh roh! Gotta fix the days!
Fixing the Days
Right now, there is a Foreach-Object that will run n times 0..($DaysCount - 1) | ForEach-Object { . And there is foreach() that will run 1 time for each day. That is redundant and confusing. Time to fix!
function New-Timesheet {
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[datetime]$StartOfHours = "8:00AM",
[datetime]$EndOfHours = "5:00PM",
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1), # Monday
[timespan]$Interval = "01:00" # One hour intervals
)
# Set the start of week to midnight to make the StartOfHours addition clean
$StartOfWeek = $StartOfWeek.Date
[timespan]$startOffset = $StartOfHours - $StartOfHours.date
[timespan]$endOffset = $EndOfHours - $EndOfHours.date
# Days of the week
foreach ($run in 0..($DaysCount - 1)) {
$day = $StartOfWeek.AddDays($run)
$time = $day.Add($startOffset)
do {
[PSCustomObject][Ordered]@{
Date = $time.ToShortDateString()
Start = $time.TimeOfDay
End = $time.Add($Interval).TimeOfDay
}
$time = $time.add($Interval)
} while ($time -lt $time.Date.Add($endOffset))
}
}
Skipping Days
Now that intervals are working nicely, lets make it so that you can exclude days of the week. We’ll do it by using a [DayOfWeek] array and a while loop that gives the advantage of editing the number of iterations to be run (unlike a foreach loop). I also added a switch parameter so that only business days will be considered (-ActiveDaysOnly).
function New-Timesheet {
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[datetime]$StartOfHours = "8:00AM",
[datetime]$EndOfHours = "5:00PM",
# Monday
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1),
# One hour intervals
[timespan]$Interval = "01:00",
# Monday-Friday, via DayOfWeek that aren't Sunday or Saturday
[System.DayOfWeek[]]$ActiveDays = ([System.DayOfWeek]::GetNames([System.DayOfWeek]) | Where-Object { $_ -notin "Sunday", "Saturday" }),
[switch]$ActiveDaysOnly
)
# Set the start of week to midnight to make the StartOfHours addition clean
$StartOfWeek = $StartOfWeek.Date
[timespan]$startOffset = $StartOfHours - $StartOfHours.date
[timespan]$endOffset = $EndOfHours - $EndOfHours.date
# Back up one day to negate the first addition
$day = $StartOfWeek.AddDays(-1)
# Days of the week
while ($DaysCount -gt 0) {
$DaysCount--
$day = $day.AddDays(1)
if ($day.DayOfWeek -notin $ActiveDays) {
if ($ActiveDaysOnly) {
$DaysCount++
}
continue
}
$time = $day.Add($startOffset)
do {
[PSCustomObject][Ordered]@{
Date = $time.ToShortDateString()
Start = $time.TimeOfDay
End = $time.Add($Interval).TimeOfDay
}
$time = $time.add($Interval)
} while ($time -lt $time.Date.Add($endOffset))
}
}
We can see it properly skipping the weekend days (Saturday and Sunday) while also creating the full number of days here:
PS> New-TimeSheet -DaysCount 3 -EndOfHours 8am -ActiveDaysOnly -StartOfWeek 2/24/2023
Date Start End
---- ----- ---
2/24/2023 08:00:00 09:00:00
2/27/2023 08:00:00 09:00:00
2/28/2023 08:00:00 09:00:00
Now that this is working, you can create a nice and full timesheet like so:
PS> New-TimeSheet -DaysCount 10 -ActiveDaysOnly
Allow Admin Time
What if you need a break after each time slot for admin time or something? Lets just add some extra interval in!
function New-Timesheet {
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[datetime]$StartOfHours = "8:00AM",
[datetime]$EndOfHours = "5:00PM",
# Monday
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1),
# One hour intervals
[timespan]$Interval = "01:00",
# Time used after each interval
[timespan]$AdminTime = "0:00",
# Monday-Friday, via DayOfWeek that aren't Sunday or Saturday
[System.DayOfWeek[]]$ActiveDays = ([System.DayOfWeek]::GetNames([System.DayOfWeek]) | Where-Object { $_ -notin "Sunday", "Saturday" }),
[switch]$ActiveDaysOnly
)
# Set the start of week to midnight to make the StartOfHours addition clean
$StartOfWeek = $StartOfWeek.Date
[timespan]$startOffset = $StartOfHours - $StartOfHours.date
[timespan]$endOffset = $EndOfHours - $EndOfHours.date
# Back up one day to negate the first addition
$day = $StartOfWeek.AddDays(-1)
# Days of the week
while ($DaysCount -gt 0) {
$DaysCount--
$day = $day.AddDays(1)
if ($day.DayOfWeek -notin $ActiveDays) {
if ($ActiveDaysOnly) {
$DaysCount++
}
continue
}
$time = $day.Add($startOffset)
do {
[PSCustomObject][Ordered]@{
Date = $time.ToShortDateString()
Start = $time.TimeOfDay
End = $time.Add($Interval).TimeOfDay
}
$time = $time.add($Interval).Add($AdminTime)
} while ($time -lt $time.Date.Add($endOffset))
}
}
PS> New-TimeSheet -DaysCount 1 -EndOfHours 8:45am -Interval 0:10 -AdminTime 0:05
Date Start End
---- ----- ---
2/27/2023 08:00:00 08:10:00
2/27/2023 08:15:00 08:25:00
2/27/2023 08:30:00 08:40:00
Wrapping Up
That’s enough for today. You could change the formatting or make it something that is dynamic from parameters. But for now, I’ll just add some comment based help and call it good.
Final Function
function New-Timesheet {
<#
.SYNOPSIS
Creates a set of objects that you can use for a timesheet
.DESCRIPTION
Outputs a series of start and end times for a day bound by rules
.PARAMETER DaysCount
The number of days to be in the timesheet (use -ActiveDaysOnly to skip non-active days)
.PARAMETER StartOfHours
The time that should begin each day
.PARAMETER EndOfHours
The time that no intervals should start on or after
.PARAMETER StartOfWeek
The day of the week that begins the timesheet
.PARAMETER Interval
How long each object should span
.PARAMETER AdminTime
How long after each interval that should be skipped
.PARAMETER ActiveDays
The days of the week that should be included
.PARAMETER ActiveDaysOnly
Makes it so that non-active days don't count against `DaysCount
.EXAMPLE
PS> New-TimeSheet -DaysCount 1 -EndOfHours 8:45am -Interval 0:10 -AdminTime 0:05
Date Start End
---- ----- ---
2/27/2023 08:00:00 08:10:00
2/27/2023 08:15:00 08:25:00
2/27/2023 08:30:00 08:40:00
.EXAMPLE
New-TimeSheet -DaysCount 3 -EndOfHours 8am -ActiveDaysOnly -StartOfWeek 2/24/2023
Date Start End
---- ----- ---
2/24/2023 08:00:00 09:00:00
2/27/2023 08:00:00 09:00:00
2/28/2023 08:00:00 09:00:00
.NOTES
Src: https://blog.dcrich.net/post/2023/generate-timesheet/
#>
[CmdletBinding()]
param(
[parameter(ValueFromPipeline)]
[int]$DaysCount = 5,
[datetime]$StartOfHours = "8:00AM",
[datetime]$EndOfHours = "5:00PM",
# Monday
[datetime]$StartOfWeek = (Get-Date).AddDays( - (Get-Date).DayOfWeek + 1),
# One hour intervals
[timespan]$Interval = "01:00",
# Time used after each interval
[timespan]$AdminTime = "0:00",
# Monday-Friday, via DayOfWeek that aren't Sunday or Saturday
[System.DayOfWeek[]]$ActiveDays = ([System.DayOfWeek]::GetNames([System.DayOfWeek]) | Where-Object { $_ -notin "Sunday", "Saturday" }),
[switch]$ActiveDaysOnly
)
# Set the start of week to midnight to make the StartOfHours addition clean
$StartOfWeek = $StartOfWeek.Date
[timespan]$startOffset = $StartOfHours - $StartOfHours.date
[timespan]$endOffset = $EndOfHours - $EndOfHours.date
# Back up one day to negate the first addition
$day = $StartOfWeek.AddDays(-1)
# Days of the week
while ($DaysCount -gt 0) {
$DaysCount--
$day = $day.AddDays(1)
if ($day.DayOfWeek -notin $ActiveDays) {
if ($ActiveDaysOnly) {
$DaysCount++
}
continue
}
$time = $day.Add($startOffset)
do {
[PSCustomObject][Ordered]@{
Date = $time.ToShortDateString()
Start = $time.TimeOfDay
End = $time.Add($Interval).TimeOfDay
}
$time = $time.add($Interval).Add($AdminTime)
} while ($time -lt $time.Date.Add($endOffset))
}
}