Using VisualCron Server 8.5.1.

I'm importing a tab-delimited text file to Excel using the Excel Create task. I've been unable to import a column of dates and have them work as dates when I open the file in Excel. If I have the dates in human-readable format in the text file, they come in as text. If I have the dates as numbers and specify any column format other than "#", they come in as numbers formatted as text.

Is there a way to import the date and have it (a) stored as a number and (b) formatted like "mm/dd/yyyy"?

Using VisualCron Server 8.5.1.

I'm importing a tab-delimited text file to Excel using the Excel Create task. I've been unable to import a column of dates and have them work as dates when I open the file in Excel. If I have the dates in human-readable format in the text file, they come in as text. If I have the dates as numbers and specify any column format other than "#", they come in as numbers formatted as text.

Is there a way to import the date and have it (a) stored as a number and (b) formatted like "mm/dd/yyyy"?

I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?

---

I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?

I used the Columns tab under the Excel - Create tab to specify all the columns.

  Excel Create Task Date Formats.zip (17kb) downloaded 42 time(s).

Here's a zip file containing the input text file and several cases of outputs. The column in question is "date_sent", which is saved in the text file as a double-precision decimal. All Excel files are created using the Excel - Create task with output as Excel 2007.

Case 1 - Column format set to "@" (default)
Case 2 - Column format set to "#.##"
Case 3 - Column format set to "M/d/yyyy"
Case 4 - Column format set to "yyyy'-'MM'-'dd'T'HH':'mm':'ss"

Only in Case 2 is the date_sent column data presented as numeric. In all other cases, it's number formatted as text, so the recipient of the file has to manually convert the data to numeric and apply the date format to it.

I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?

I used the Columns tab under the Excel - Create tab to specify all the columns.

  Excel Create Task Date Formats.zip (17kb) downloaded 42 time(s).

Here's a zip file containing the input text file and several cases of outputs. The column in question is "date_sent", which is saved in the text file as a double-precision decimal. All Excel files are created using the Excel - Create task with output as Excel 2007.

Case 1 - Column format set to "@" (default)
Case 2 - Column format set to "#.##"
Case 3 - Column format set to "M/d/yyyy"
Case 4 - Column format set to "yyyy'-'MM'-'dd'T'HH':'mm':'ss"

Only in Case 2 is the date_sent column data presented as numeric. In all other cases, it's number formatted as text, so the recipient of the file has to manually convert the data to numeric and apply the date format to it.

Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.

---

Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.

In Windows, I right-click on the text file and open it with Excel. Then I format the date_sent column with a date/time format. That's the problem; no matter how I prepare the data before running the Excel - Create task, I still have to manually convert or re-format the dates after they're in the Excel file.

If I were going to send the text file instead of an Excel file, I would just write the date_sent data as a date string. Excel converts it automatically and gives it a date/time format.

Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.

In Windows, I right-click on the text file and open it with Excel. Then I format the date_sent column with a date/time format. That's the problem; no matter how I prepare the data before running the Excel - Create task, I still have to manually convert or re-format the dates after they're in the Excel file.

If I were going to send the text file instead of an Excel file, I would just write the date_sent data as a date string. Excel converts it automatically and gives it a date/time format.

How is the text file generated from start, via VC or something else?

Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.

---

How is the text file generated from start, via VC or something else?

The text file is created by a PowerShell script using the ConvertTo-Csv and Out-File cmdlets.

Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.

I've given up and switched to using the ImportExcel PowerShell module to create the Excel file programmatically. I had hoped the VC solution would be as simple as "You just need to format the number like this in your text file and then use this column format in the Excel - Create task." Since that doesn't appear to be the case, I've made a workaround.

How is the text file generated from start, via VC or something else?

The text file is created by a PowerShell script using the ConvertTo-Csv and Out-File cmdlets.

Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.

I've given up and switched to using the ImportExcel PowerShell module to create the Excel file programmatically. I had hoped the VC solution would be as simple as "You just need to format the number like this in your text file and then use this column format in the Excel - Create task." Since that doesn't appear to be the case, I've made a workaround.

I just wanted to notify you that we've fixed this issue now, and it should convert your numbers properly to a date (in Columns you have to set the data type as "Numbers")

The fix is currently implemented in our 8.5.6 beta, which you can find in the beta section here on the forums.

---