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.

what is this number format TT.MM. hh:mm, it is not a valid format in Excel. — cyboashu
– cyboashu, Commented Dec 26, 2017 at 17:23
@cyboashu Your comment is right for the non localized (or en-US) version of Excel. If the display language is set to another value, you may get other valid format strings. — harper
– harper, Commented Dec 26, 2017 at 18:40
@cyboashu That date-time will be displayed as "27.12. 13:45" if you entered this with the GUI. But I need to setbit by script. — harper
– harper, Commented Dec 26, 2017 at 18:46
Display language is "German (Deutschland)" also shown as DE-DE. — harper
– harper, Commented Dec 26, 2017 at 18:49

cyboashu · Accepted Answer · 2017-12-26 19:08:53Z

2

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.

answered Dec 26, 2017 at 19:08

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

harper Over a year ago

If I read the CategoryType instead of setiing it, I get already the value 2. That's no surprise, because the data column the is the source for the x-axis is already a list of date-time values. Well with that background I could not get any help from your answer. Thank you, anyway.

michael · Accepted Answer · 2017-12-26 19:04:19Z

0

Use NumberFormatLocal instead of NumberFormat to get/set a localized format:

$axis.TickLabels.NumberFormatLocal = $fmt

answered Dec 26, 2017 at 19:04

michael

9596 silver badges19 bronze badges

Collectives™ on Stack Overflow

Assign Excel number format from string variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related