Assign Excel number format from string variable

Question

I create an Excel workbook using a PowerShell script. The workbook consists a chart with two axes. The number format for one axis shall be set to a localized custom date-time-format.

The standard format is prepared as a shorter version of the long date format:

[string]$fmt = "MM/dd hh:mm"

The script evaluates the automatically created axis NumberFormat member and uses a different string if the NumberFormat is already localized:

if ($axis.TickLabels.NumberFormat.Contains("JJJJ")) {
  $fmt = "TT.MM. hh:mm"
}

Finally the script tries to assign the string to the axis member:

 $axis.TickLabels.NumberFormat = $fmt

But this format is not accepted by the axis object. If I read back the value I get "Standard".

Even this code resets the number format to standard:

 $fmt = $axis.TickLabels.NumberFormat
 $axis.TickLabels.NumberFormat = $fmt

How can I set a custom date-time-format?

Edit: The standard date-time-format for the localization DE-DE is "TT.MM.JJJJ hh:mm". Excel uses this as default if the chart is created. That format is valid for DE-DE even it may be unknown in your Excel localization.

Answer 1

The issue is not with number format.

You need to set your axis type to Text before you can add a date to it and apply custom format (with time bits) on top of that.

This works:

$chart.Axes(1).CategoryType =2
$axis= $chart.axes(1)
$axis.TickLabels.NumberFormat = "DD.MM. hh:mm"

where as if you leave the Axix category to default/automatic, the number format doesn't work as expected.

$axis= $chart.axes(1)
$axis.TickLabels.NumberFormat = "DD.MM. hh:mm"

You have a time format in your number formatting and apart from scatter charts nothing else support that on an axis. So you need to first change your axis type.