Excel help - weird one
Discussion
Hi all,
Having an issue with a date field.
To the eye they look fine, it’s only when you try and filter its displaying 2019 as a collapsible tree and then separately a number of 2019 dates – to me this means it’s not seeing them both as 2019 which means there is something in the data that is different for those entries.
If you cut the column, paste into notepad and then back into excel it fixes it, which confirms there is something in the data which notepad strips, to me.
it's not a space, its not an ALT-0010 or paragraph character and using 'clear all formatting and then reformatting as date doesn't fix it either.
any ideas?
Having an issue with a date field.
To the eye they look fine, it’s only when you try and filter its displaying 2019 as a collapsible tree and then separately a number of 2019 dates – to me this means it’s not seeing them both as 2019 which means there is something in the data that is different for those entries.
If you cut the column, paste into notepad and then back into excel it fixes it, which confirms there is something in the data which notepad strips, to me.
it's not a space, its not an ALT-0010 or paragraph character and using 'clear all formatting and then reformatting as date doesn't fix it either.
any ideas?
PixelpeepS3 said:
Hi all,
Having an issue with a date field.
To the eye they look fine, it’s only when you try and filter its displaying 2019 as a collapsible tree and then separately a number of 2019 dates – to me this means it’s not seeing them both as 2019 which means there is something in the data that is different for those entries.
If you cut the column, paste into notepad and then back into excel it fixes it, which confirms there is something in the data which notepad strips, to me.
it's not a space, its not an ALT-0010 or paragraph character and using 'clear all formatting and then reformatting as date doesn't fix it either.
any ideas?
Are you importing it from a CSV file? If so, and with VBA, you might need to force it to know that it's a date in the UK format, not the US format.Having an issue with a date field.
To the eye they look fine, it’s only when you try and filter its displaying 2019 as a collapsible tree and then separately a number of 2019 dates – to me this means it’s not seeing them both as 2019 which means there is something in the data that is different for those entries.
If you cut the column, paste into notepad and then back into excel it fixes it, which confirms there is something in the data which notepad strips, to me.
it's not a space, its not an ALT-0010 or paragraph character and using 'clear all formatting and then reformatting as date doesn't fix it either.
any ideas?
It's related to Local:= True or something similar...
louiebaby said:
Are you importing it from a CSV file? If so, and with VBA, you might need to force it to know that it's a date in the UK format, not the US format.
It's related to Local:= True or something similar...
it is coming from a CSV, created in Ireland.It's related to Local:= True or something similar...
how do i force it to know its a UK date?
PixelpeepS3 said:
it is coming from a CSV, created in Ireland.
how do i force it to know its a UK date?
For Excel 2010, rather than opening your CSV file, create a new workbook, then on the DATA tab, select Get External Data → From Text. This gets to the interface where you can specify how to interpret your text data, including how to handle dates.how do i force it to know its a UK date?
Taken from: https://superuser.com/questions/408432/how-to-stop...
PixelpeepS3 said:
it is coming from a CSV, created in Ireland.
how do i force it to know its a UK date?
If above suggestion doesn't work, tryhow do i force it to know its a UK date?
Highlight the column in Excel, go to Date->Text to Columns->Delimited->uncheck any delimiters selected and hit next->Under the Column Data Format, select Date, then pick DMY-> Finish.
Edited by hyphen on Thursday 29th August 17:14
PixelpeepS3 said:
louiebaby said:
Are you importing it from a CSV file? If so, and with VBA, you might need to force it to know that it's a date in the UK format, not the US format.
It's related to Local:= True or something similar...
it is coming from a CSV, created in Ireland.It's related to Local:= True or something similar...
how do i force it to know its a UK date?
https://a4accounting.com.au/vba-to-open-csv-and-av...
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff