CSV output file changes date format

[mcinnes01]

Hi,

I don't seem to have much look with dates changing from UK to US format...

This time it is a totally different circumstance in that my data is output to a CSV file. The dates are corrent in UK format on the original workbook, but when I check the output CSV file the dates have changed to mm/dd/yyyy.

The strange thing is that there is another column on the CSV output file that has a description and 2 date fields merged to make a dated description

E.g.

Description - Petrol
Date from - 01/10/2010
Date to - 20/10/2010

Result - Petrol: 01/10/2010 - 20/10/2010

This is column 2 in the output CSV file, and the date display correctly.

Below is my code for this function,

Please Login or Register  to view this content.

[romperstomper]

How are you checking the output CSV file - in notepad or in Excel?

[mcinnes01]

I have been checking the output file in excel

[romperstomper]

Don't - Excel may convert the data when it opens the file. Check it in Notepad instead. If it's still wrong, then we'll have to review the code - the CSv file should pick up whatever number formatting you had applied.

[mcinnes01]

Seems to be working now, even if I view it in excel. Strange

The text file had the correct format, does this mean if I import it in to the database it will bring in the correct UK format then?

Andy

[romperstomper]

Never mind, I think I can see the problem with the code. Change the last two lines to:

Please Login or Register  to view this content.

[romperstomper]

If the text file is correct, then the db should be fine (assuming the import process doesn't do any conversions)

[mcinnes01]

Hi,

That seems to be working thanks, Just wondered I have made a slightly different version of this sheet for recording overtime, the CSV function works fine but the output tab is slightly different.

For a basic explination, with overtime calcs if someone is part time say 20/35 and they do 20 hours overtime.

The first 15 hours are paid at time and get one GL Code
The other 5 are time and a half and get a different code.

On my input sheet this is all calculated and only fills in the relevant line in the table (essentially there are about 12 overtime types)

The way the form worked originally was that it copied the data from the single input line to the output tab, this one now copies from the new table I've created to the output tab and works fine...


However it copies the whole range of the table "I6:P18" and includes blank lines on the output table. How can I get it so it only copies lines out of that range that aren't blank?

This is the current copy code I am using:

Please Login or Register  to view this content.

[romperstomper]

That's a completely different question, so please start a new one.

[mcinnes01]

no problems