Manual Change Data Format in Excel (After Writing Data)
This is a simple method that you should use if you don't mind doing post-processing in Excel after writing your data.
You can change the date format after completion of writing data to Excel by selecting a different locale. Excel chooses the default regional date format (i.e. MM/DD/YYYY, DD/MM/YYYY, etc.) based on your current location setting. In Excel, you can
manually change the format of a particular cell by following Windows' instructions.
Convert the Date Representation to Text
If you prefer accomplishing the task programmatically, you can change the format of text before writing a timestamp by using Report Generation VIs by writing data in the
text data type instead of
timestamp datatype.
This will change the way Excel handles the cell data from the date format to a simple text string.
=TEXT(A1,"dd/mm/yyyy")
Figure 1: Time Format Snippet
Note: This image is a LabVIEW snippet, which includes LabVIEW code that you can reuse in your project. To use a snippet, right-click the image, save it to your computer, and drag the file onto your LabVIEW diagram.
Use an Excel Template
If you will be writing multiple data sets
You can manually change a cell's format in Excel, and save the worksheet as a template. If you create your Excel report in LabVIEW from this template, the cells will use the formatting you set. To do so, please follow below steps:
- Open a new worksheet in Excel. Select the cells that will hold dates and set the date format you want.
- Save the worksheet as an Excel template.
- In LabVIEW, place a New Report.vi function.
- Create a control on the template (empty) input and select the Excel template you created above.
- Create a constant on the report type input and select Excel.
- Place an Excel Easy Text.vi function.
- Create a control on the text input to enter your date.
- Enter your date in the format =DATE(YYYY,MM,DD)
- Place an Excel Bring to Front.vi function to open Excel once the worksheet has been edited.
- Connect all report and error inputs and outputs.
When run, the snippet attached below will place the Date input into cell A1 of the new worksheet. This worksheet will use the same date format for cell A1 that was set in the Excel template. The snippet from Figure 2 shows the example Excel Date Formating.vi
Figure 2: Excel Date Formating Snippet
Note: This image is a LabVIEW snippet, which includes LabVIEW code that you can reuse in your project. To use a snippet, right-click the image, save it to your computer, and drag the file onto your LabVIEW diagram.
Excel® is a registered trademark of Microsoft.