Date and Time format

[silentwolf]

Hi guys,

just facing problems with an importet file.

Can someone tell me how I need to custom format so it shows the correct time in excel

2017-12-30-09.28.28.937036 so it shows the date and time correctly? Many thanks for your help! Albert

<colgroup><col width="176"></colgroup><tbody>
</tbody>

 

[Gerald Higgins]

Hi, I tried Steve the Fish's solution in post #3 , it worked for me, but I guess there is probably some regional setting you need to change, not just the ; for , replacement.

Here's a slightly different approach . . .

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

NOTE - this IGNORES the final fraction of a second, in other words rounds the time DOWN to the nearest full second.
If that's a problem for you, there are ways of rounding it differently.

 

[silentwolf]

Hi Gerald,
thanks for your help.
The formula I was able to update to german but how do I need to change the format now..??

Sorry about this.

Thanks

 

[Gerald Higgins]

Open the "Format Cells" dialog box, for example by right clicking on the cell.
Select the "Number" option, and then choose "Custom".

Then in the "Type" box, key in a format, such as
YYYY-MM-DD HH:MM:SS

Some of this is probably different in German, sorry !

 

[silentwolf]

Hi Gerald,

many thanks for your help! Much appreciated! And yes I did work it out the englisch german translation
It is a bit different but all good now!

Cheers