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]

Bebbspoke · Dec 4, 2017

Hi Wayne - this is insane??? I copy pasted your correction:-

With colB =

<tbody>
[TD="class: xl63, width: 191"]2017.11.07

gives colC =

<colgroup><col width="191"></colgroup><tbody>
[TD="class: xl65, width: 191"]07/.1/ 201

the result is the same regardless as to whether there is any space iin the mid() epart of the expressio i.e. mid(B1,6,2) as in your line #2 or mid(B1, 6,2) as given in the full formula???

I am using Office 2016. There is something really weird happening!

further suggestion much appreciated! Cheers, John
[/TD]

</tbody>

[/TD]

</tbody>

etaf · Dec 5, 2017

something else is going on
spaces - maybe need to use trim

see next post

etaf · Dec 5, 2017

Sheet1

	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:214px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]2017.11.29[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]Extracted using MID[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]29/11/2017[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]Test to check date is correct B5+1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]30/11/2017[/TD]

</tbody>

Spreadsheet Formulas

Cell	Formula
B5	=RIGHT(B1,2)&"/"&MID(B1, 6,2)&"/"&LEFT(B1,4)
B8	=B5+1

Excel tables to the web >> Excel Jeanie HTML 4

Bebbspoke · Dec 5, 2017

Hi Wanye - I believe I have found the cause for the anomaly - (but have no idea how to fix it (... without ploughing through what could bee >20k data lines): -

All the colB date entries are preceded by a blank space!?! i.e. colB1= 2017.11 24 as opposed to colB=2017.11.24

any suggestions as to how to remove the blank space automatically such that the cell contents are fully recognised would be mach appreciated, thank you!

etaf · Dec 6, 2017

does every entry have 1 space at the front

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

however if some do and some dont , then we may need a IF or we could usea helper column and use trim

in a new cell type
=trim(b1)

then change the formula to use the new cells

OR

=IF(LEN(B1)=11, RIGHT(B1,2)&"/"&MID(B1,7,2)&"/"&MID(B1,2,4),RIGHT(B1,2)&"/"&MID(B1,6,2)&"/"&LEFT(B1,4))

Bebbspoke · Dec 6, 2017

Hi Wayne - thank you for your response.
ALL data entries are preceded by a space and your given formula (line#2 last message) is successful in converting to correct numeric date e.g. 27/11/2017

however - then requesting such to display as ddd/mm/yyyy i.e. Mon/11/2017 cannot be achieved despiste trying various general, date or custom formats...

The size of the data strings may be >20k - my (eventual) aim is to be able to sort this into individual day and/or hour sequencesfor further analysis with other variables.

Cheers, John

etaf · Dec 6, 2017

try
=VALUE(RIGHT(B1,2)&"/"&MID(B1,7,2)&"/"&MID(B1,2,4))

Bebbspoke · Dec 6, 2017

Wanye - EUREKA!!! - thank you very much for your patience and persistence!

Cheers, John

Bebbspoke · Dec 6, 2017

Hi Wayne - I'm still having some sorting problems: - having (with your assistance) achieved separation of individual named days & times - I seek to list events in (say) order of named day...
however - Excel seems determined to the full chronological sequence and thus does not list all (say) all Mondays events... then those of all Tuesdays etc but lists them as cosecutive weeks.
Surely I'm not asking something Excel is unable to process? thanks.

etaf · Dec 6, 2017

the date is a number with 1st jan 1900 as day 1
then each day increments the number by 1

therefore excel sees all dates as a number

when you format as DD/MM/YYYY you are only changing the way excel displays the number and it will not therefore sort by all the Tuesdays together , because excel does not know the cell as tuesday - ONLY as a number
BUT if you formatted as
DDD
only then it will sort by the days of the week

Date &/or time sort

Bebbspoke

Board Regular

Bebbspoke

Board Regular

Excel Facts

etaf

Well-known Member

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Bebbspoke

Board Regular

etaf

Well-known Member

Bebbspoke

Board Regular

Bebbspoke

Board Regular

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Date &/or time sort

Board Regular

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

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