how can I format this time value?

[cat3appr]

Hi all!

please see the attached xls:

i need to format the date/time column in the left, that comes out only as a time ---> format it as the column in the right, which is a timestamp with year month, day and 00.000 seconds all in one value without separation, how do i do this?

many thanks!

[vogel997]

Try this
how-can-i-format-this-time-value-date-and-time-formatting.xlsx

[Glenn Kennedy]

I read the OPs request as being the other way round. Col A the source.. col E the result and got confused as there's no date in col A, whatever way I looked at it...

[newdoverman]

This should convert your values in column E into hours, minutes and seconds.

Format as h:mm:ss

Please Login or Register  to view this content.

[martindwilson]

14:33:00 20140706224625.083
how is 20140706224625.083 made up?

20140706 maybe 2014 7 June or 6 July depending on regional settings

224625.083 how is that a time?

[newdoverman]

My thought was that the decimal part was a decimal fraction of a second which the example left off so I left it out also and just gave the seconds without the decimal.

[cat3appr]

newdoverman , thank you but I need the other way around I need to convert the date value in column A as column E, I need something like that 14:33:00 ----> 201407061433 , I believe that the YYY:mm:dd are already embedded in the 14:33:00 .... if this is not the case I'd just need to add YYY:mm:dd next to the 14:33:00 and format it as 201407061433

Glenn Kennedy , thank you, I believe that there is the YYY:mm:dd in the values of column A... i'm not 100% sure... i tried to extrapolate but I wasn't able to... as per request above, if this is not the case I'd just need to add YYY:mm:dd next to the 14:33:00 and format it as 201407061433

thank you!

[AlKey]

Maybe this one

=TEXT(INT(A1),"yyyymmdd")&TEXT(MOD(A1,1),"hmm")

	A	B
1	8/30/2014 11:17	201408301117

[newdoverman]

Maybe something like this will do what you want. This is to the nearest second but what the decimal is indicating is anyone's guess at this point. I used the current year, the month and day indicated (hard coded into the formula as text) then appended the time.

Please Login or Register  to view this content.

[Glenn Kennedy]

It makes me feel better to see you guys stumbling round a bit, too...

[newdoverman]

The problem with this question is that there isn't a date given just a time so the first 8 characters are at best a guess as there is no basis given to determine what these characters should be.

[Glenn Kennedy]

Exactly... see post 3.

[vogel997]

how-can-i-format-this-time-value-date-and-time-formatting.xlsx

newdoverman , thank you but I need the other way around I need to convert the date value in column A as column E, I need something like that 14:33:00 ----> 201407061433 , I believe that the YYY:mm:dd are already embedded in the 14:33:00 .... if this is not the case I'd just need to add YYY:mm:dd next to the 14:33:00 and format it as 201407061433

Glenn Kennedy , thank you, I believe that there is the YYY:mm:dd in the values of column A... i'm not 100% sure... i tried to extrapolate but I wasn't able to... as per request above, if this is not the case I'd just need to add YYY:mm:dd next to the 14:33:00 and format it as 201407061433

thank you!

Date imbedded
=TEXT(A8,"yyyymmddhhmmss.000")

Date not imbedded
=TEXT(A2+B2,"yyyymmddhhmmss.000")