Amazon Unified Transaction Report Date Format

Selling on Amazon Reports
1

The report does not sort properly by date and will not reformat to a date option. It sorts the months alphabetically.

Many years ago on the older OSFE, a kind fellow seller gave me this tip to overcome that problem, and it does work:

a) In the imported worksheet, insert a blank column before column A so that the “date/time” data in now in column B.
b) In cell A2, enter – rather, copy/paste!!! – this formula:

=MONTH(1&LEFT(B2,3))&“/”&TRIM(SUBSTITUTE(MID(B2,5,3),“,”,“”))&“/”&TRIM(MID(B2,8,5))

c) If you like what you see, copy that formula downcolumn, holding relative refs from B2.

d) Now you can perform a natural sort on column A.

BUT it displays the date numerically, ie: 3-12-24.

I have formulas based on a date display with the 3 letter month abbreviation, ie: Mar 12, 2024 (same as the report shows without the time).

Is there a forum Excel genius that can help me get the alphabetical & numerical date value to display and sort in true date order?

My goal is:
Jan 2, 2024
Jan 3, 2024
Jan 4, 2024
Feb 1, 2024
Mar 11, 2024
etc
or anything similar as long as it uses the 3 letter month abbreviation and sorts in correct month and day order.

3 Likes
2

May I ask which version of MS Office you’re using that produces this error?

3

Office 365

The person who gave me the instructions also wrote this in the post:

Handling as a standard CSV file, the CTReport will still have the complex date in column A, due to the " cell qualifiers in the raw data.
There isn’t a “simple” deformatting to apply to the concerned cells.

We componetize and reconstruct to the desired date format as part of script (macro equivalent, but we use a variety of programming languages depending on the deployment need). That is probably overkill.

2 Likes
4

Highlight the column you wish to format date.
On the Home tab, look for the drop down that is showing “General”
Click at the very bottom where it says More Number Formats
In the next screen on the left menu, click “Custom” at the bottom
In the entry field under Type put “mmm d, yyyy” (quotation marks not entered)
Click ok button at bottom

Every date in that column should now be formatted to your Jan 2, 2024

FYI

We normally set the date to yyyy-mm-dd. This allows for the sort to be by year, then month and then day.

2 Likes
5

It is not an error but how that sheet is dated formatted in that column from the imported file.

1 Like
6

Yes, I’ve tried that because it is how it is normally done but it doesn’t actually sort correctly.
I tried using this for my sort:
image

and still Feb is before Jan because alphabetically F is before J.

And the date numbers are sequential as to the first number:

Jan 9, 2024 9:26:06 PM PST
Jan 9, 2024 9:48:29 AM PST
Mar 1, 2024 10:09:56 AM PST
Mar 1, 2024 10:10:11 AM PST
Mar 10, 2024 10:32:21 PM PDT
Mar 10, 2024 11:43:24 AM PDT

1 Like
7

Yes that is what I was told years ago

I wondered if there was a way to alter this formula to display the 3 letter month instead of it’s number

1 Like
8

Excel sort for date will work best if done as numbers with the months in two digits, days in two digits and year in 4 digit.

Excel will sort the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 21, 22 as 1, 10, 11, 2, 21, 22, 3, 4, 5, 6, 7, 8, 9

On the months done in letters, you would need to set up a reference that sets JAN to 01, Feb to 02, etc and override the alphabetical sort.

From our experience, the most efficient way to sort dates is to run the date as 2024-01-05 (example format).

2 Likes
9

I thought this might be what is needed, I’m not entirely sure how to do it. I have many ‘WHATIF’ formulas that look for the 3 letter month name to aggregate data from the different categories into monthly totals so that is why I want to keep the month name instead of the number.

2 Likes
10

No genius here.

First, I am going to look at what what posted to learn something new.

But this is what I do

Assuming the column says something like
Jan 23, 2024 9:56:56 AM PST

  1. Find/Replace 2024 with 2024#
  2. Text to column - Delimited - Other (with #).

(Text to column - alllows to put the result in any cell so as not to overwrite. But most times I just copy paste the whole column to another worksheet - do my thing, then copy paste back)

As to getting your data to say DEC, or December, or 12 or MDY, or YMD Simply format the column.

3 Likes
11

The data from Amazon has Jan, Feb, Mar, etc but Excel does not recognize it as a date format (and cannot reformat it), so it sorts alphabetically (Feb before Jan) instead of in date order.

1 Like
12

My solution works - did you try?

2 Likes
13

Using Conditional Formatting of cells for the date sort will work if you set the date to be the yyyy-mm-dd

But it will be a quicker and easier as a simple sort of the column when the date is yyyy-mm-dd

If the date is imported as Jan 1, 2024, it would be easier to set the column (as we suggested in post 4 above) and then use the sort under “Sort & Filter”

It is less likely to cause excel to loop lock up.

1 Like
14

Is the sheet locked?

If so, copy the sheet using copy then paste values to the new sheet (so you don’t pull the Amazon formatting with it). You will then be able to format the date column.

2 Likes
15

Yes I tried it and it did work! Simple to do, thank you.

3 Likes
16

No it is not locked.

1 Like
17

Great - a visual for others

Screen Shot 2024-04-16 at 7.29.57 PM
Screen Shot 2024-04-16 at 7.29.57 PM793×693 240 KB

3 Likes
18

I tried the custom format suggested before I created this thread, it still did not sort in proper order.
Paste - Values Only into a new sheet would work, but going forward with more months and more data to come I think Chimanimani’s solution is the most elegant.

Thanks for thinking this through with me!

4 Likes
19

I actually did it all in the original column, without using a secondary sheet as an intermediary. It kept the 3 letter month name too - And it sorted correctly!!!
:smiley:
:clap: :clap: :clap:

I knew there were geniuses on this forum. A great resource.

3 Likes
20

It’s funny how there are a number of ways to skin a cat. I use left / right functions to split the data apart and sort however I like.

3 Likes