rangequestion
Board Regular
- Joined
- Nov 21, 2016
- Messages
- 62
Hi,
I have an example of a table below that is retrieved from a web based platform in a .csv format. The problem I'm having is that the "Transaction Date" field can show 2 different formats (examples below) and neither of which are in a date format. They come to me as a European format, not US. Excel reads one as custom and the other as General. T
he second issue I'm having is that the Transaction amounts that I get aren't in any sort of number format, again they come custom. I'm trying to extract the absolute value in "Transaction Amount Country Currency".
Is there some sort of formula or macro that would create an additional 2 reference columns for Transaction Date in MM/DD/YYY format and Transaction Amount Country Currency and get the absolute value of that cell?
thanks!
<tbody>
</tbody>
I have an example of a table below that is retrieved from a web based platform in a .csv format. The problem I'm having is that the "Transaction Date" field can show 2 different formats (examples below) and neither of which are in a date format. They come to me as a European format, not US. Excel reads one as custom and the other as General. T
he second issue I'm having is that the Transaction amounts that I get aren't in any sort of number format, again they come custom. I'm trying to extract the absolute value in "Transaction Amount Country Currency".
Is there some sort of formula or macro that would create an additional 2 reference columns for Transaction Date in MM/DD/YYY format and Transaction Amount Country Currency and get the absolute value of that cell?
thanks!
| Transaction ID | Transaction Date | Account ID | Debit/Credit | Transaction Amount Base Currency | Transaction Amount Country Currency | Transaction Type | Transaction Channel | Originating Customer | Originating Bank | Beneficiary Customer | Beneficiary Bank | Mention | Processing Date |
| XXXXX | 26/07/2016 00:00:00 | XXXXX | Debit | -€ 4,999.95 | -USD 5,000.00 | XXXXX | XXXXX | XXXXX | XXXXX | XXXXX | XXXXX | "Long String", see below | 27/07/2016 |
| XXXXX | 12/7/2016 12:00:00 AM | XXXXX | Debit | -€ 13,000 | -USD 15,000 | XXXXX | XXXXX | XXXXX | XXXXX | XXXXX | XXXXX | "Long String", see below | 13/07/2016 |
<tbody>
</tbody>
