convert variable number (time stamp) to time format

[cmacnab]

Hi, I have a report that gives me the time stamp of a transaction in the format of a number. The format is hours, minutes, seconds and milliseconds. I'm trying to covert this to a time format however the number of digits varies (from 4 to 8 digits). For example:


9494300 would be 9:49:43:00AM
253800 would be 00:25:38:00AM
60300 would be 00:06:03:00AM
2400 would be 00:00:24:00AM
12473600 would be 12:47:36:00PM16094500 would be 16:09:45:00PM

I'm trying to find a formula to convert all of these variable numbers to 00:00:00:00AM or PM. I could easily lose the last 2 zeros as they aren't important. Can someone help with this.

Thanks in advance.

 

[shg]

	a​	b​	c​
2​	9494300​	9:49:43 am​	b2: =--text(a2/100, "00\:00\:00")
3​	253800​	12:25:38 am​
4​	60300​	12:06:03 am​
5​	2400​	12:00:24 am​
6​	12473600​	12:47:36 pm​
7​	16094500​	4:09:45 pm​