Date and time in one cell, and duplicate rows from overlapping bank exports, in Excel™
Two small problems turn up when you stitch bank exports together. Some banks put the date and the time in one cell, so a lookup on the date finds nothing. And if two exports cover overlapping periods, the overlap appears twice.
Date and time in one cell
If the cell is text such as 03/04/2026 14:32, split it. If Excel™ has already converted it to a date and time value, the date is the whole-number part.
These steps apply to Excel™ in Microsoft 365. Work on a copy.
- For text, split on the space:
=LEFT(A2,FIND(" ",A2)-1)for the date and=MID(A2,FIND(" ",A2)+1,20)for the time. - Convert the date text deliberately, with the day-first reading you have confirmed, not with a default.
- For a true date-time value, use
=INT(A2)for the date and=A2-INT(A2)for the time. - Format each column as Date or Time.
Duplicates from overlapping exports
- Add a key column that joins the fields that identify a transaction, for example date, description and amount:
=TEXT(A2,"yyyy-mm-dd")&"|"&B2&"|"&C2. - Add
=COUNTIF(D:D,D2)and filter for counts above 1. - Look at each group. Two genuine transactions can share a date, description and amount, such as two identical purchases in one day, so do not delete blindly.
- Keep the rows from the overlapping period once.
Flag both in the scan
UK Bank CSV Cleaner flags date and time in one cell (BK-06) and duplicate rows from overlapping exports (BK-15), along with a running balance that does not follow the amounts (BK-16). The scan and explanations are free, and the workbook is not uploaded.
See UK Bank CSV Cleaner for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. UK Bank CSV Cleaner is not affiliated with or endorsed by Microsoft, any bank or any accounting software supplier.