Extract Day from Date returning Month instead of Day

**Epscan** · 03-05-2013, 02:05 PM

I have a program that is creating a csv file and dumping data and a date stamp into a file.
The date format that is exported out is dd/mm/yyyy.

I then have a macro running that copys the cells from the csv file into my workbook.

When I try to extract the day from the date I am getting the month instead of day. This is only the case if the day of month is less than 12 after that the day is correct.

I have included a sample of the day to date errors.

What am I missing?

**SDCh** · 03-05-2013, 02:34 PM

When I entry manual with format dd/mm/yyyy, on your excel that already has date format the result like what you want.
If you want to know your data are correct or not, change the format date into general number.
From your example for G12 and G14 it still show "03/14/2013" not a number, the other 41397 that belong to "03-05-2013" (dd/mm/yyyy)

When you copy the data you should use : G(X)= Date(year,month,day). Note: X = row number
So the excel will entry the data with a correct value.

**Norie** · 03-05-2013, 03:00 PM

The dates in G6:G8 are all 03 May 2013, so the day is 3.

Are you doing anything with the dates in the CSV?

How are you copying from it to the workbook?

**Epscan** · 03-05-2013, 05:37 PM

I thought that if I had a date format of mm/dd/yyyy and if I would enter month/day/Year 03/05/2013 manually then the day should be the 5th.
It does appear that excel is converting it to the 3rd for some reason.

Unfortunalty the csv file is generated off site and I will not be back there for about a week to check on how it is populated. I do know that each month a blank csv is created and the column with the date in is supposed to have dd/mm/yyyy formating.

I use a macro to open the csv file and select the cells and copy and paste into a worksheet in which I have formatted the cells to dd/mm/yyyy.
From there I extract the day out for display elsewhere in spreadsheet.
Upon closer inspection it appears that some of the raw data has the date format (days 1-12) and the others have general format (days 13-31). I will check to see if this is a software issue.

**Norie** · 03-05-2013, 05:47 PM

If you enter 03/05/2013 then the day will be the 5th, assuming your system date format is mm/dd/yyyy.

Could it be possible that it's been changed to the dd/mm/yyyy format?

How exactly are you copying/importing/whatever the CSV into Excel?

You might need to 'tell' Excel what format the dates in the CSV are in.