Date/time format unchangeable

[Draconia]

Hello Everyone,

I have a folder in which a lot of small files are stashed (ooutput of an apparatus) from which i need (among others) the date/time value. My macro opens each file copies the date/time and puts it in a table. From there i do all bunch of stuff with the data.

Problem is that the date/time format of the copied cell is not the right one. However i can't change it using format cell/date. It stays the same. If i enter the cell, put the cursor before the numbers and put backspace, the cell goes into the right format. This i cannot do using the find/replace function, that doesn't work.

I suspect that there is a hidden format which i copy with it, because if i copy the data to notepad and back, the format is gone and i can use the data. Alas notepad I cannot reach from within excel and VBA...

Does anyone know how i can make it work again, lose the format or change the value so that i can make it a date again?

thank you in advance,

Draconia

 

[combo]

Your dates/times are stored as text. One of the ways of conversion without re-entering:
- format range as date,
- copy any empty cell.
- select range to convert,
- paste special values with operation 'add'.

If you have problem with date order, you can try the 'text to columns' conversion, after initial steps it is possible to set DMR order in converted text.

combo

 

[PHV]

My macro opens each file copies the date/time and puts it in a table
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Draconia]

found it. The original files were password protected therefor the cell format was hidden and unchangeable. After removing the protection I could remove the format and add the wanted format.
Thanks for your time though