0

When I try to export in excel a list of incidents from HPSM I have an issue; the date has different formats: "dd/mm/yyyy hh:mm" or "mm/dd/yyyy hh:mm:ss". It is a problem because when the day and the month are both less the 12th then I don't know which is month or which is day. Is it a possibility to have the same format or to create a program to do this?

User
  • 13
  • 5
  • Why are you creating both DMY and MDY worksheets/workbooks? Typically, an entire office uses one or the other and it isn't until regional areas are crossed that the format changes. btw, the supplied date formats available that start with an asterisk (e.g. `*`) will change depending on what the regional settings of the particular computer it is on from DMY to MDY and back again. –  Aug 31 '16 at 06:26
  • @Jeeped the date is from two different regions, but my question was: can I create a program to transform this date to have the same format? – User Aug 31 '16 at 06:43
  • If you are asking permission then by all means, please go ahewad. If you are asking how then use the [Range.NumberFormat property](https://msdn.microsoft.com/en-us/library/office/ff196401.aspx). If you are asking me to do it for you then you have hte wrong web site. –  Aug 31 '16 at 06:46
  • @pnuts I don't have – User Aug 31 '16 at 07:04
  • Examine the file in Notepad, or a text editor. Do you see different date formats in the **same** file? Or do they all appear the same? If the latter, then use the Text Import Wizard in Excel to tell Excel the proper format before hand, or change your Windows regional setting to match. See also @teylyn's answer below. – Ron Rosenfeld Aug 31 '16 at 10:03

1 Answers1

0

I don't know HP Service Manager, but please consider exporting the data into two different files, one for each date setting, as CSV or Excel.

Then you can use tools like Power Query or the Text Import Wizard to open the files and set the origin date format of the column. Excel will then show that date column in your PC's regional settings format.

teylyn
  • 34,374
  • 4
  • 53
  • 73