Convert Date to Excel Date dd/mm/yy hh:mm:ss

[thesilkster]

I have a large dataset that is taken from a warehouse management system unfortunately
the dates are not friendly 20-Feb-2016 11:27:10 so when i use a pivot it cannot group on the dates? i tried doing ctr+f and replace all the - but this did not work and taked forever with 100,000 rows of data

any help is appreciated

 

[Chihiro]

Can you upload sample with some data? It will be much easier to help you then.

Without the sheet, here's couple of suggestions that you can try.
1. Add helper columns to extract Month, Day and year. Then group based on those.
2. Try using =VALUE(I2) and see if it returns numeric value or error.

If it returns error, it confirms that date is stored as text and not date value. You will need to use text function to manipulate it. Or if it returns numeric value... try using =INT(I2) to strip down to date info only and group using that.

 

[thesilkster]

Hi thanks for your quick reply it would appear that they are in some sort of a date form i have added a sample file the yellow columns are vlookups to the order number this file gets rather big as you can imagine.

 

[vletm]

@thesilkster
Test this Macro ...

Sub tst()
    Range("A1").CurrentRegion.Replace " ", "§", xlPart
    Range("A1").CurrentRegion.Replace "§", " ", xlPart
End Sub

 

[Chihiro]

@vletm
Nice code to force Excel to update format. Never thought of replacing char and reverting to force update.

@thesilkster
Your data has date as value, format is the only issue.

This often happens when data is imported from another source. If VBA isn't an option (company policy etc). Alternately, you can use text to column with fixed width (entire column length), and choose date as format and update entire column in one shot.

 

[thesilkster]

Hi Chihiro thanks i have created a macro that will select the columns and convert text to columns Hi Vletm the code didn't work for me all it did was insert§ between 12-Feb-20016§13:00.

could i use a relationship using order id to look at both tables? once the date is sorted

 

[Chihiro]

You should be able to. Just make sure both data sets are converted to table format. Then you can use "Relationships" button in DATA ribbon to link (assuming you are using Excel 2013).

See links for some step by step guide and pointers.
http://www.techrepublic.com/blog/10-things/10-steps-to-using-excel-2013s-new-data-modeling-feature/

https://support.office.com/en-us/ar...add-a629-8a3848820be3?ui=en-US&rs=en-US&ad=US

 

[vletm]

@thesilkster
Did You have both lines of code?
The 1st line changes " " to § and 2nd line changes § to " ".
Or is it again that 'PC/MAC' ...
test this for both versions...