I have data that is imported and need to convert that dates to dd/mm/yyyy format. As can be seen some of the ageing is negative and cannot be negative
I have used the following macro, to connvert the dates so that the ageing is correct, but cannot get it right
It would be appreciated if someone could kindly assist me in resolving this
I have used the following macro, to connvert the dates so that the ageing is correct, but cannot get it right
It would be appreciated if someone could kindly assist me in resolving this
Code:
Sub TextToDMY()
With Sheets("Imported Data")
With Range("T2:T" & .Cells(Rows.Count, "T").End(xlUp).Row)
.TextToColumns Destination:=.Cells(1), _
DataType:=1, TextQualifier:=1, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
End With
End With
End Sub
Excel 2010 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | |||
1 | Date Processed | Total Invoice Value | Total VAT Value | Outstanding | Status | Narrative Created By | Narrative | Ageing | 16/06/2015 10:37 | |||||||
2 | 25/02/2015 00:00:00 | -10.9907 | 0 | -10.9907 | Unpaid | 111 | ||||||||||
3 | 25/02/2015 00:00:00 | -49.0867 | 0 | -49.0867 | Unpaid | 111 | ||||||||||
4 | 25/02/2015 00:00:00 | -40.6933 | 0 | -40.6933 | Unpaid | 111 | ||||||||||
5 | 25/02/2015 00:00:00 | -40.6933 | 0 | -40.6933 | Unpaid | 111 | ||||||||||
6 | 25/02/2015 00:00:00 | -108.647 | 0 | -108.647 | Unpaid | 111 | ||||||||||
7 | 25/02/2015 00:00:00 | -108.647 | 0 | -108.647 | Unpaid | 111 | ||||||||||
8 | 25/02/2015 00:00:00 | -8.33333 | 0 | -8.33333 | Unpaid | 111 | ||||||||||
9 | 03/10/2015 00:00 | -71.666 | 0 | -71.666 | Unpaid | (109) | ||||||||||
10 | 03/10/2015 00:00 | -13.3907 | 0 | -13.3907 | Unpaid | (109) | ||||||||||
11 | ||||||||||||||||
Sheet1 |