How to get the Version Number of Microsoft Office from its Build number in Powershell

I read on my Windows based systems (via Remote Powershell) what is installed on their computers.
This creates a report. I am having some difficulties looking into the evaluation of the Microsoft Office software.

It is not hard to get the Office version from registry:

Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Office\ClickToRun\Configuration' `
          | Select-Object -ExpandProperty VersionToReport

This will output to something like 16.0.14527.20276. It is also not hard to evaluate which main version this is:

Nr	Office Version
11.0	Office 2003
12.0	Office 2007
14.0	Office 2010
15.0	Office 2013
16.0	Office 2016+ (2019 or 2022 or MS365 version)

As you can see, the 16.0 version is kind of a collection of all latest Office variants.
As logically as it might be to Microsoft, it is useless for my case.

The solution lies within the rest of the build number. These numbers specify which version of Office you have.
Clearly we are using Office 365, but I want to know whether they are all up-to-date.

With the rest of the build number, you can get that finer grain of the version.
You could look them up at this Microsoft site. There you can find for instance that Build 14527.20234 is Version 2110.

Since I am monitoring hundreds of computers, this needs to be automated.
So that everytime Microsoft creates a new version, it gets updated automatically.

How can I end up with an up-to-date array with both the Build numbers and the Version numbers?

Solution

I created a function so you can call it as many times as you need.

It will only run if it needs to OR if you force it to (parameters -ForceCSV or -Output).
I added this to prevent the script starting the lookup everytime when you -for example- are polling 50 computers at the same time. The evaluation part prevents that, but can be overriden with the two mentioned parameters.

The CSV output then can be evaluated on other systems without the need of installing or pushing this function.
To keep the answer as short as possible, I removed as much Write-Verbose and comment lines as possible.

function Get-OfficeVersion {
    [CmdletBinding()]
    param([switch]$Output,[switch]$ForceCSV)

    $URL = "https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date#version-history"
    $CsvFile = "\\server\share\OfficeVersions.csv"
    # Evaluate whether script should run
    if ( ((gci $CsvFile).LastWriteTime.Date -eq (Get-Date).Date) -and !($ForceCSV.IsPresent)) 
     {$CsvOutput = $false} else {$CsvOutput = $true}
    if ($Output.IsPresent -or $CsvOutput -or $ForceCSV.IsPresent) {
    #End Evaluation
        $VersionsSite = (Invoke-WebRequest -Uri $URL)
        $VersionTableRawData = ($VersionsSite.AllElements | Where-Object {$_.tagname -eq 'TD'}).innertext | ? {$_ -like "Version* (Build *)"}

        $Versions = @() 
        foreach ($DataItem in $VersionTableRawData) {
                $ProcessedItem = (($DataItem -replace "\)") -split "\s\(")

                $item = New-Object PSObject
                $item | Add-Member -type NoteProperty -Name 'Version'  -Value ([string]($ProcessedItem[0].ToString().split(' ')[1] -replace ' ' -replace 'Version' -replace 'Build' -replace "\n" -join ''))
                $item | Add-Member -type NoteProperty -Name 'Build'    -Value ([string]($ProcessedItem[1].ToString().split(' ')[1] -replace ' ' -replace 'Version' -replace 'Build' -replace "\n" -join ''))
                $Versions += $item
        }

        $VersionsSorted = $Versions | sort Build -Unique | sort Version -Descending | select Build,Version
        if ($Output.IsPresent) {return $VersionsSorted}
    
        If ($CsvOutput) {$VersionsSorted | Export-csv -Path $CsvFile -Force -NoTypeInformation -Delimiter ','}
    }
}

As a bonus, I also do the evaluation part of this list.
First you get the Office Version:

$OfficeVersionX32        = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Office\ClickToRun\Configuration' -ErrorAction SilentlyContinue -WarningAction SilentlyContinue) | Select-Object -ExpandProperty VersionToReport
$OfficeVersionX64        = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\WOW6432Node\Microsoft\Office\ClickToRun\Configuration' -ErrorAction SilentlyContinue -WarningAction SilentlyContinue)
if ( $OfficeVersionX32 -ne $null -and $OfficeVersionX64 -ne $null) {
  $OfficeVersion = "Both x32 version ($OfficeVersionX32) and x64 version ($OfficeVersionX64) installed!"
} elseif ($OfficeVersionX32 -eq $null -or $OfficeVersionX64 -eq $null) {
  $OfficeVersion = $OfficeVersionX32 + $OfficeVersionX64
}
$OfficeVersionMain = $OfficeVersion.Split(".")[0]
$OfficeVersionSub1  = $OfficeVersion.ToString().Replace("16.0.","")

Which you then can use to evaluate the system.
As told, for version numbers 11.0 untill 15.0 it is easy, the difficulties start at version 16.0.

$CsvFile = "\\server\share\OfficeVersions.csv"
Switch  ($OfficeVersionMain) {
    11      {$MSOffice ="Office 2003 ($OfficeVersion)" }
    12      {$MSOffice ="Office 2007 ($OfficeVersion)" }
    14      {$MSOffice ="Office 2010 ($OfficeVersion)" }
    15      {$MSOffice ="Office 2013 ($OfficeVersion)" }
    16      {
                $LatestOfficeVersions = Import-Csv $CsvFile
                $MSOffice = "Office 365 (Version $(($LatestOfficeVersions | ? {$_.Build -eq $OfficeVersionSub1}).Version), $OfficeVersionSub1)"
            }
    default {$MSOffice = $OfficeVersion}
    $null   {$MSOffice = "No Office installed."}
}

And with the output $MSOffice you can continue. It should be of the form "Office 365 (Version 2110, 14527.20234)".