Hey folks I am trying to get my taxes done and running into a frustrating hitch on finishing my book keeping.
The dates that come out of Paypal are formatted day, month year. However when I try to sort them it views sorts them by the first number - so all the 30th’s are together which isn’t helpful. I have tried formatting it using the options under the brush tool but it does not understand what it is seeing and swaps the month for the day.
It does work okay when it comes in at first but I have to do a bunch of edits that are easiest by sorting other columns to clean up the data.
If you have any control over the data coming out of PayPal, @jec0047’s suggestion will be the easiest solution. If not, it would probably be best to transform the data into something Numbers can parse. To do this several questions need to be answered, like: How does your data come out of PayPal? Is it a CSV file? an Excel file? something else? Is it formatted with slash separators? hyphen separators? Do the days and months have leading zeros? Is the year four digits or two?
A small sample of the data that comes out of PayPal would be helpful, but we don’t want to see any of your real financial information. Could you edit the data to keep the dates as-is but with fake values in the other fields? Just a few records should be enough to understand the formatting.
Even if it comes out of Paypal as text, if you select the column in Numbers you can change the formatting to date and in my experience it will figure out the right date. Then you can sort it.
Thanks for all suggestions folks. After digging around more I found custom formatting. Under cell, then formating (it was automatic) I changed it to custom. If you change the option from text to date and time it will allow you to tell numbers what the date format is with a drag and drop set of options.
So I was able to specify that 05/01/2021 was Jan 5th 2021. then the sorting function is smart enough to take it from there.
Hmm. May have spoken too soon. It keeps shifting back to mixed formatting and the wrong sorting. These dates should be June 1st but I can’t get them to show that way. It came as a CSV out of Paypal but I can’t see any options to adjust it on the export settings there.
Good timing on this post, I have a related question. I want to insert a date formula that changes the date each day down the column like this:
3/20/22
3/21/22
3/22/22
I want the dates to be automatic so I don’t have to enter them each time I make an entry.
I’ve tried these but they don’t change the subsequent date to match the date for each day. What am I doing wrong? The dates showing are those I entered manually. I’d like this to be automatic.
The dates look the same all the way through the file.
I am not adding any data on my side just using the file to look at info for tax stuff. Essentially I need to see who lives in Canada and who doesn’t for tax entries. QBO doesn’t import that part of the data so I am comparing entries in QBO against the spreadsheet i got from Paypal.
Enter your start date in a field either manually or using the TODAY function. In the field below it, enter a formula which is your first field + 1. Then copy that field with its formula down the rest of the column.
So if you put the TODAY function in cell A4, you should end up with $A4 + 1 in cell A5, $A5 +1 in cell A6, and so on.
If you use the TODAY() function, the field will update every time that you open the file (I believe); if you started the file today with TODAY()'s date you would have 22 March. When you open the file tomorrow, the date will update to 23 March and all the subsequent rows would also increase as they are offsets from the first row. This may not be a problem if the data is a one off, but your first suggestion of entering a fixed date in th first cell and then adding 1 for each row would get around this issue, if there is only one entry (row) per day and all entries are for concurrent days.
I’ve made the following assumptions about the CSV file that comes from PayPal:
The dates are not surrounded by quotation marks.
The dates use slashes to separate day, month, and year.
The dates use leading zeros when necessary to make the days and months two characters long.
The dates use a 4-digit years.
There is a single header line at the top of the file.
From there, I put together a simple Quick Action Shortcut called Fix PayPal Dates that converts the dates from dd/mm/yyyy format to yyyy-mm-dd. Numbers should understand this format and parse it correctly.
After installing the Shortcut from the link above, right click on the CSV file you got from PayPal and select Fix PayPal Dates from the Quick Actions submenu. It will create a new CSV file on your Desktop with the same name as the original but with “new” added to the front.
If it doesn’t work, it’s likely that one or more of my assumptions are wrong. The code can be adjusted.
