Date &/or time sort

Bebbspoke · Dec 4, 2017

[FONT=&quot]Hi peoples: wrt date and time sorting – I receive bulk data from various sources which use different formats for stating event date and time – usually given as one column, e.g.: -

2017-11-29 13:25:49 but maybe 29.11.17 13:25 (or other variants) and usually this data is formatted as "general".

Please – how should one proceed to sort via (say) specific days of the week or hours of the days – my problem is deriving the relevant day or hour of the event from the column source.

Thank you, Bebbspoke

[/FONT]

etaf · Dec 4, 2017

are those formats going into excel as a date or as text
you say Its formatted as general

When you import the data into excel , you should be able to format the column as a date

If its text , then you will need to change the text into a date
what are all the varients you get ?
if they are all in the form of

DD MM YY
then you can use text formulas like MID or LEFT or RIGHT to modify
BUT
2017-11-29 13:25:49
should be seen as a date anyway

Bebbspoke · Dec 4, 2017

etaf - thank you for your response - the variants of souced date & time are too numerous to list.
The main problem is to deconcatenate - trouble is is you search deconcatenate - you only get results for concatenation!
... the MID or LEFT or RIGHT options are not conducive to extracting the time information....

etaf · Dec 4, 2017

I'm afraid I dont know a solution, if the formats are to numerous to list, sorry , hopefully someone else may have a solution for you

Bebbspoke · Dec 4, 2017

etaf - thank you for trying - all I require is to deconcatente to sparate dates and times... and then get the dates to read as days...
I find it most frustrating that there are no rational instructions or advice on any of the help sites...
Sure - I (eventually) discover how to deconcatenate... but the dates refuse to format as text.
I do much complex data analysis with Excel... but the simple requirements appear ridiculously difficult to achieve...!

So please - what is the instruction to achieve a date given as (e.g.) 2017.11.23 to procure "Thu" in the subsequent column?, thank you.
#

etaf · Dec 4, 2017

you need 2017.11.23 to be seen by excel as a date - first and not by text
which involves MID/LEFT/RIGHT

Bebbspoke · Dec 4, 2017

I have separated the date & time into different cols - however - the date remains as 2017.11.14 regardless of attempts to change the format.
as you can probably guess - I find this exceedingly tiresome...

etaf · Dec 4, 2017

=Right(cell,2)&"/"&Mid(Cell, 6.2)&"/"&Left(cell,4)
where cell is the cell with 2017.11.14 in

Bebbspoke · Dec 4, 2017

Wayne - ok - so I COPY your formula - and change each cell occurence as apprporiate...

i.e. =Right(B1,2)&"/"&Mid(B1, 6.2)&"/"&Left(B1,4)

returns "You've entered too few arguments for this function"

Suggestions, please?

etaf · Dec 4, 2017

the mid( ) should read
mid(b1,6,2)
not 6.2

=RIGHT(B1,2)&"/"&MID(B1, 6,2)&"/"&LEFT(B1,4)

you did say
... the MID or LEFT or RIGHT options are not conducive to extracting the time information....
so i assume you have tried those functions

Date &/or time sort

Bebbspoke

Board Regular

Excel Facts

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Date &/or time sort

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock