Excel Date Formats

[LGMan]

Hi within a file exported as csv from SQL, there is a date field that appears as dd/mm/yyyy hh:mm:ss, so 06/01/2016 13:30:47
or as a general field as 42375.56304. It previously appeared as 06-Jan-16. I don't own the SQL code that creates the table where the data is exported from unfortunately

I would like to have just dd-mmm-yy and not have any hours or seconds.
So is it possible to copy and paste just the dd-mmm-yy or use Text to Columns or another way?
The reason being, I use another formula to workout the correct weekend date for that date,which is then used in a pivot.
Thanks.

 

[SkipVought]

HI,

It appears that you opened the text file with Excel, and Excel makes the text to number conversions in a default manner.

I almost always IMPORT rather than OPEN a text file, via Data > Import external data> Text files...

Via this method you will be able to assure that the text to number conversions will be executed in accordance with your specifications.

You can, however, use Data > Data tools > Text to columns > Delimited on SPACE, on the DateTime column and Do not import column (Skip). This, in effect, strips the decimal portion of the number.

Or you can create a new column for the date alone using the INT() function to return the integer part of the DateTime value.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macropod]

there is a date field that appears as dd/mm/yyyy hh:mm:ss, so 06/01/2016 13:30:47 or as a general field as 42375.56304. It previously appeared as 06-Jan-16.

This is just a matter of formatting. 42375.56304 is the numerical value stored by Excel for 06/01/2016 13:30:47. If you change the cell format to dd/mm/yyyy, what you'll see is 06/01/2016. Whether that's sufficient for your analysis may depend on whether your other formula needs to work with just the date value, which is the integer component (i.e. 42375).

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

@Paul, but the underlying value still has the fractional part. NumbrtFormat changes nothing but the display value.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macropod]

Granted, but the OP also said:

It previously appeared as 06-Jan-16.

To me, that implies the OP has a format issue, not a content issue.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Ah. "Previously" appeared WHERE?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

The 06-Jan-16, is how it appeared in a csv export of a SQL report. So it was just the date and no hours mins or seconds. The export now contains the extra granularity, however that messes up a routine that hangs off of the csv. The issue that I was having is that the dates are apportioned to week ending dates, so the data can be summarised into weeks. The extra granularity was giving me a week ending date of 10-Jan-16 10:21:35 for example.

Anyway, the issue has now be solved with the Text to Columns help that you (Skip) and combo provided over on the VBA forum.