Timestamp - Won't convert to serial

[finnexcel]

I have an excel sheet related to orders.

I am trying to work out the difference between two timestamps that look like the below.

30/03/2012*23:54:00
30/03/2012*23:38:00

Im in UK - hence formatting. I just cant get them to convert to serials.

I have tried using text to columns to shave off the seconds - and then format > custom> dd/mm/yyyy hh:mm etc...

I also tried starting a new custom format to incorporate seconds.

Tried a =now() and got timestamp below.

31/03/2012 10:32

Any Idea what I'm doing wrong? - been searching for ages.

 

[finnexcel]

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?)

 

[p45cal]

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?)

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?

 

[finnexcel]

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

 

[finnexcel]

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!

 

[p45cal]

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.)

 

[finnexcel]

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)

 

[p45cal]

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

 

[finnexcel]

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

Sorry - a bit too keen

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

 

[p45cal]

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.)