Timestamp - Won't convert to serial

F

finnexcel

New Member

Joined

Jan 7, 2012

Messages

19

• Mar 31, 2012

• #2

30/03/2012*23:59:00

This is a cut and pasted timestamp from the excel I have to work from - I dont see the asterisk when I type this - noticed the * in the post.

(not sure if that is the issue?)

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Mar 31, 2012

• #3

finnexcel said:

30/03/2012*23:59:00

This is a cut and pasted timestamp from the excel I have to work from - I dont see the asterisk when I type this - noticed the * in the post.

(not sure if that is the issue?)

Click to expand...

It could be..
If the date/time stamp is a true excel date/time then you shouldn't have to do any converting to serial - just subtract one from the other.
You can tell if it is a true excel date by changing the format of the cell to General (if it's already General then it isn't an excel date/time) and for that date time above you should see:
40998.9993055556

If that asterisk is some other character, you could try editing the cell, deleting the (invisible?) character and putting a space character in instead, then pressing Enter. Is it a date now?

 

Upvote 0

F

finnexcel

New Member

Joined

Jan 7, 2012

Messages

19

• Mar 31, 2012

• #4

Wow!

Yes it is - some kind of invisible asterisk in there!

Is there a way I can use a formula to parse somehow the faulty timestamps I have into proper timestamps? (got 30k to work with)

Thanks so much

 

Upvote 0

F

finnexcel

New Member

Joined

Jan 7, 2012

Messages

19

• Mar 31, 2012

• #5

re: If this has helped and you can't be bothered to acknowledge it, then I can't be bothered to look at further posts from you.

Cant work out how to get thanks button on here

But thanks!

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Mar 31, 2012

• #6

Well, let's find out what that character is first:

Excel Workbook
	J	K
25	30/03/2012*23:59:00	42
Sheet

I've put an asterisk in there, and the code for an asterisk is 42. Numbers and '/' and ';' characters are in the range 47 to 58, a space is 32. So you're looking for values outside these.
In the formula the number 11 is the eleventh character, you may need to adjust that 11 a bit to find the right character.
Once we know this it should be easy to find and replace the whole range at once.

(Acknowledgement isn't necessarily 'thanks', it's just a response of any kind, and only sought if I helped.)

 

Upvote 0

F

finnexcel

New Member

Joined

Jan 7, 2012

Messages

19

• Mar 31, 2012

• #7

Thanks

I can follow the thinking but not implementation.

I got as far as the below - started new thread as different question - but shouldnt have done



If I manually edit cell - backspace then press spacebar - they convert fine

Tried using =replace(a1,11,1," ")

But it didnt work.

Am I missing something - or do I need to find and replace by hex values and not by position in cell and replace with " "?

(you have definitely helped - muchos brownie points at work if I can get this to work)

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Mar 31, 2012

• #8

One step at a time. What value do you get for the errant character using instructions given in msg#6?

 

Upvote 0

F

finnexcel

New Member

Joined

Jan 7, 2012

Messages

19

• Mar 31, 2012

• #9

p45cal said:

One step at a time. What value do you get for the errant character using instructions given in msg#6?

Click to expand...

Sorry - a bit too keen

=CODE(MID(B10,11,1)) = 160

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Mar 31, 2012

• #10

It's a non-breaking space.
Select all your cells with the dates in (DON'T just select a single cell, it will do a find/replace ond the whole sheet!).
Ctrl+H on the keyboard
In the find what: field do the following:
hold down the alt key and on the NUMERIC KEYPAD type 0160 then release the alt key.
In the Replace with: field type in a single space.
Click Replace all.
You may need to adjust the formatting of the result.
(If you're on a laptop with no numeric keypad, use the Num or NumLock function on that keyboard.)

 

Last edited: Mar 31, 2012

Upvote 0