Sorting data datewise
- Thread starter paradise
- Start date
XLPadawan
Member
I can only add this - be sure to change the Date dropdown to DMY (in Step 4):
Test this way...
1) Select range D2:D10
2) Data > Text to Columns...
3) <Next>, <Next>
4) Select Column Data Format 'Date'
Change the Date dropdown from MDY to DMY
5) Keep 'Destination' same
6) <Finish>
Test this way...
1) Select range D2:D10
2) Data > Text to Columns...
3) <Next>, <Next>
4) Select Column Data Format 'Date'
Change the Date dropdown from MDY to DMY
5) Keep 'Destination' same
6) <Finish>
Peter Bartholomew
Well-Known Member
The enclosed is generated by formulas in Office 365 with conditional formatting to highlight the January dates.
The key that is applicable to any version of Excel is the DATEVALUE function. The output table contains the Excel dates rather than text.
Can u pls enclosed the same in above file.
Peter Bartholomew
Well-Known Member
I can upload the file but it will only work if you have Office 365 and set it to update beta release versions by using the 'insider' settings.
H
Currently,I am using MS office 2016/19version.How to convert the same to Office 365 or get updated to that features.
Oh!How to install the same.Can u kindly,send me the link.
Currently,I am using MS office 2016/19version.How to convert the same to Office 365 or get updated to that features.
Peter Bartholomew
Well-Known Member
A license for Office 365 can either be bought online from Microsoft or from a computer store. Unlike 2016/2019 which are one-off purchases of a perpetual licence, 365 comes on a subscription basis with annual payments. I believe any Office installation later than 2010 will simply be replaced by the subscription license and reverting would require you to install the old version from scratch.
The insider program is selected when setting up and choosing update frequency. If it is to be your main copy of Office you will need to bear in mind that your work will not be immediately transferable to others and, since it is a beta release scheme, the weekly updates you receive may not be entirely stable, though I have experienced no difficulty to date.
It took me a certain amount of soul-searching before I decided to commit and purchase an additional license. For me, it is pretty close to how spreadsheets should always have worked, though I can see it might come as a bit of a shock to some!
There are also some other interesting functions available, FILTER for example.
= VLOOKUP( fKey, table, {1,2}, 0 )
would return a record from a two-column table but, then again, so would
= FILTER( table, key=fKey,"missing")
The latter would also return multiple records if they matched.
Similarly
= SUMIFS( value, key, fKey )
returns the sum of any matches but so would
= SUM( FILTER( value, key=fKey, 0) )
The insider program is selected when setting up and choosing update frequency. If it is to be your main copy of Office you will need to bear in mind that your work will not be immediately transferable to others and, since it is a beta release scheme, the weekly updates you receive may not be entirely stable, though I have experienced no difficulty to date.
It took me a certain amount of soul-searching before I decided to commit and purchase an additional license. For me, it is pretty close to how spreadsheets should always have worked, though I can see it might come as a bit of a shock to some!
There are also some other interesting functions available, FILTER for example.
= VLOOKUP( fKey, table, {1,2}, 0 )
would return a record from a two-column table but, then again, so would
= FILTER( table, key=fKey,"missing")
The latter would also return multiple records if they matched.
Similarly
= SUMIFS( value, key, fKey )
returns the sum of any matches but so would
= SUM( FILTER( value, key=fKey, 0) )