M
mralmackay
Hi,
Could anyone possibly assist with the following. I've been unable to
'Group' items together, by date, in a pivot table and I now understand
why.
A small handful of the dates, which are fed in from another
spreadsheet by a macro, are not correctly formatted. It's not
possible to amend these either by Format change. Most of the dates
appear as dd/mm/yyyy hh:mm, however a number of these also have an
extra space between yyyy and hh for some reason. These are extracted
from an ERP system so I can't understand why this occurs, but wondered
if there's anything I can do in excel to 'correct' these.
I've tried adding a helper column to the spreadsheet to contain a
formula, however either my logic is wrong or it's still not possible
to do this based on how the data actually appears. Formula was:
=IF(ISERROR(MID(K8,1,FIND("
",K8,1))),MID(K8,1,4)&"0"&MID(K8,4,9),MID(K8,1,FIND(" ",K8,1)))
On screen also the field content actually is displayed as, e.g.
03/8/2007 09:25, so 'on screen' it's even missing the 0 before the 8
as well. However if you enter into the cell this is displayed.
Appreciate any help you can offer on this as this is really confusing
me. I only need the date, not the hh:mm either - however I've just
tried formatting this way as well incase this helped it - it didn't!.
Thanks in advance, Al.
Could anyone possibly assist with the following. I've been unable to
'Group' items together, by date, in a pivot table and I now understand
why.
A small handful of the dates, which are fed in from another
spreadsheet by a macro, are not correctly formatted. It's not
possible to amend these either by Format change. Most of the dates
appear as dd/mm/yyyy hh:mm, however a number of these also have an
extra space between yyyy and hh for some reason. These are extracted
from an ERP system so I can't understand why this occurs, but wondered
if there's anything I can do in excel to 'correct' these.
I've tried adding a helper column to the spreadsheet to contain a
formula, however either my logic is wrong or it's still not possible
to do this based on how the data actually appears. Formula was:
=IF(ISERROR(MID(K8,1,FIND("
",K8,1))),MID(K8,1,4)&"0"&MID(K8,4,9),MID(K8,1,FIND(" ",K8,1)))
On screen also the field content actually is displayed as, e.g.
03/8/2007 09:25, so 'on screen' it's even missing the 0 before the 8
as well. However if you enter into the cell this is displayed.
Appreciate any help you can offer on this as this is really confusing
me. I only need the date, not the hh:mm either - however I've just
tried formatting this way as well incase this helped it - it didn't!.
Thanks in advance, Al.