Date format in write range

Help
,
1

I am reading a csv file into a datatable. The datatable comprises of 4 columns each of type string. One of the columns reads a date from the csv file in format dd/MM/yyyy.

When I write the resulting datatable to the output screen the column displays as 08/05/2017 as expected however when I write the datatable to excel it becomes 42953 (the date in number format).

if I cut the output of the write line and paste it into excel manually I don’t get the same issue. The date remains as a string. The conversion seems to occurs during the write range step.

What steps do I need to take to ensure the write range maintains the date string in its same format?

thanks

2

You will need to ensure the format of the cells in the Excel Workbook is correct. Either that or output to CSV but ensure that you have formatted it correctly in the datatable first. When you print it out to screen are you formatting it when you display it?

1 Like
3

I create a new sheet in excel when outputting the datatable so the format of the cells is General. When I write to console/output I output the datatable to a string Variable and write.

This only happens when the date string fits an american date format. For my 08/06/2017 I get 42953 but for 13/06/2017 I get 13/06/2017.

Additionally if I loop through the datatable and write cell for each value, the date appears in the correct format when I open the excel sheet so this issue appears to only impact write range.

4

One workaround would be to use a template Excel file - copy it and rename it then write to it rather than creating a brand new sheet. Then worst case, if the cells still don’t format, you can embed a macro in it and force the cells into the right format. Not a great solution but will work.

2 Likes
5

thanks Richard. I’ve ended up creating a macro to change the date format of a selected field. I can use select range to select the fields and run the macro. I just need to make sure the macro is available on any machine running the bot

6

Maybe a little bit late but you can lock the sheets format.
Try this: Protecting a Worksheet's Format (Microsoft Excel) worked good for me!

1 Like