If you look on your bank history, the date given by Replay is given in a format as follows:
Mmm dd, h:mm[am|pm]
for dates in the current year, but for dates from pre-Jan1, they are as follows:
Mmm dd yyyy, h:mm[am|pm]
If you paste this value into a spreadsheet, it will not be able to convert it to a proper datetime format. It will only know it as a text value, which means that you can’t do neat stuff like sorting and Date functions on them. This limits how useful they are, to “not very”.
Let’s fix this.
Below, I’ve pasted the top two rows of my spreadsheet, showing how I handle converting the date given on the bank history page to a proper datetime value. I accomplish this using text parsing functions and a number of helper columns:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Change | Type | Description | Total | Converted Date | Adjusted Total | Aggregate Bonuses | YYYY | MM | DD | hh | mm | AM/PM | all-in-one date |
| 2 | May 20, 6:02pm | 47,500 | Tournament | Prize from mtt: 3613068 – SnG Heads Up - May 20 2020, 5:30pm | 27,361,531 | 2020/5/20 | 15,737,031 | 11,624,500 | 2020 | 5 | 20 | 6 | 02 | pm | 5/20/2020 18:02:00 |
| 3 | May 20, 6:02pm | 47,500 | Tournament | Prize from mtt: 3613068 – SnG Heads Up - May 20 2020, 5:30pm | 27,361,531 | =DATE(I2, J2, K2) + TIME(IF(EXACT(N2, “am”), L2, L2+12), M2, 0) | 15,737,031 | 11,624,500 | =IF(FIND(“,”, A2)<=7, YEAR(NOW()), RIGHT(LEFT(A2, FIND(“,”, A2)-1), 4)) | =SWITCH(LEFT(A2, 3), “Jan”, 1, “Feb”, 2, “Mar”, 3, “Apr”, 4, “May”, 5, “Jun”, 6, “Jul”, 7, “Aug”, 8, “Sep”, 9, “Oct”, 10, “Nov”, 11, “Dec”, 12) | =LEFT(MID(A2, FIND(" “, A2) + 1, FIND(”,“, A2) - FIND(” ", A2) -1), 2) | =MID(A2, FIND(“,”, A2)+2, FIND(“:”, A2) - FIND(“,”, A2) -2) | =MID(A2, FIND(“:”, A2)+1, 2) | =RIGHT(A2, 2) | =DATE(IF(FIND(“,”, A2)<=7, YEAR(NOW()), RIGHT(LEFT(A2, FIND(“,”, A2)-1), 4)), SWITCH(LEFT(A2, 3), “Jan”, 1, “Feb”, 2, “Mar”, 3, “Apr”, 4, “May”, 5, “Jun”, 6, “Jul”, 7, “Aug”, 8, “Sep”, 9, “Oct”, 10, “Nov”, 11, “Dec”, 12), LEFT(MID(A2, FIND(" “, A2) + 1, FIND(”,“, A2) - FIND(” “, A2) -1), 2)) + TIME(IF(EXACT(RIGHT(A2, 2), “am”), MID(A2, FIND(”,“, A2)+2, FIND(”:“, A2) - FIND(”,“, A2) -2), MID(A2, FIND(”,“, A2)+2, FIND(”:“, A2) - FIND(”,“, A2) -2)+12), MID(A2, FIND(”:", A2)+1, 2), 0) |
These formulas are pretty tough to read, but what they do is use the string value of the date given by Replay, and find the various chunks of it that represent the month, day, year, hours, minutes, and am/pm, and then feed those into a date conversion function that returns a valid datetime value that the spreadsheet can recognize and use as such.
If the date you copied from your bank history page is prior to 1/1 of the current year, the year value will be found in the string; if not, then it is assumed to be the current year. We determine whether the string contains the year in it by counting the number of characters up to the comma – if this number is <= 7, then the YYYY value is omitted from the string and assumed to be the current year; if the comma is at the 8th index or later in the string, then the YYYY must be there, and we strip the four characters preceeding the comma to obtain the correct year.
The “Adjusted Total”, “Aggregate Bonuses” columns you may ignore; I have calculations which sum the bonus chips I’ve been awarded to date, and subtracts them from my true total so that I can see a clearer picture of how many chips I’ve won through winning hands in ring play or by cashing in tournaments. It omits the daily bonus chips for logging in, as well as any RP promotional chips awarded for various promotions. I left them in the snippet here so that the cell addresses given in the date conversion formulas are accurate.