Help Arranging Billing Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1490020%22%20slang%3D%22en-US%22%3EHelp%20Arranging%20Billing%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490020%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20that%20this%20is%20a%20really%20simple%20issue%2C%20guys%20and%20gals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20tracking%20monthly%20subscriptions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20multiple%20columns%20with%20name%2C%20billing%20date%2C%20amount%2C%20and%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20billing%20dates%20entered%20in%20a%20simple%20format%20of%201st%2C%202nd%2C%203rd%2C%20etc.%20of%20the%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20sorting%20these%20dates%20by%20order%20of%20first%20to%20last.%20%26nbsp%3BIt's%20not%20possible%20as%20it%20is%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20reformat%20so%20that%20I%20can%20easily%20rearrange%20my%20data%20by%20chronological%20progression%20into%20the%20month%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1490020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490049%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Arranging%20Billing%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490049%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710483%22%20target%3D%22_blank%22%3E%40cwatters86%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%2C%20you%20need%20to%20ensure%20your%20column%20have%20a%20headers%20on%20line%201%20(date%2C%20vendor%2C%20amount%20and%20so%20on)%2C%26nbsp%3Bthen%20select%20line%201%2C%20look%20for%20%22Sort%20%26amp%3B%26nbsp%3BFilter%22%20in%20the%20ribbon%20Home%20tab%20and%20select%20%22Filter%22.%3C%2FP%3E%3CP%3EThis%20will%20add%20the%20possibility%20to%20sort%20the%20columns%20however%20you%20want%20(chronological%20up%20or%20down%2C%20alphabetical%20up%20or%20down).%20You%20just%20have%20to%20click%20the%20arrow%20next%20to%20the%20header%20you%20want%20to%20sort%20and%20select%20the%20direction%20you%20want%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490061%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Arranging%20Billing%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710483%22%20target%3D%22_blank%22%3E%40cwatters86%3C%2FA%3E%26nbsp%3B%20I'm%20betting%20another%20person%20here%20will%20find%20a%20more%20clever%20solution%20but%20here%20is%20mine%3A%3C%2FP%3E%3CP%3Einsert%20a%20new%20column%20with%20this%20formula%20(assuming%20col%20A%20has%20the%201st%2C%202nd%2C%203rd%2C%20etc...%20text%20in%20it)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNUMBER(--LEFT(A1%2C2))%2C--LEFT(A1%2C2)%2C--LEFT(A1%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThen%20sort%20by%20this%20new%20column%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I know that this is a really simple issue, guys and gals.

 

I am tracking monthly subscriptions.

 

I have multiple columns with name, billing date, amount, and account.

 

I have billing dates entered in a simple format of 1st, 2nd, 3rd, etc. of the month.

 

I am having trouble sorting these dates by order of first to last.  It's not possible as it is now.

 

How can I reformat so that I can easily rearrange my data by chronological progression into the month?

2 Replies
Highlighted

Hi @cwatters86

First of, you need to ensure your column have a headers on line 1 (date, vendor, amount and so on), then select line 1, look for "Sort & Filter" in the ribbon Home tab and select "Filter".

This will add the possibility to sort the columns however you want (chronological up or down, alphabetical up or down). You just have to click the arrow next to the header you want to sort and select the direction you want it.

 

Highlighted

@cwatters86  I'm betting another person here will find a more clever solution but here is mine:

insert a new column with this formula (assuming col A has the 1st, 2nd, 3rd, etc... text in it):

=IF(ISNUMBER(--LEFT(A1,2)),--LEFT(A1,2),--LEFT(A1,1))

Then sort by this new column