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>

 

[James006]

Hi,

Can you expand on 09.28.28.937036 ... and explain what it represents ...

What is your expected result ?

 

[steve the fish]

You will have to convert then use custom format eg:

=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",":"),":",".",3),"-"," ",3)

would work on my machine then format to eg:

dd/mm/yyyy hh:mm:ss.000

 

[silentwolf]

Hi guys, @James I can not tell you as I am not seeing the time correctly unfortunatelly
@Steve thanks for your reply.. sorry still no luck with the forumla.. what does the

=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",":"),":",".",3),"-"," ",3)

mean?
because I am getting a value error..

Many thanks

 

[Gerald Higgins]

silentwolf - you do actually need to tell us what the expected result should be.

If you don't know what the result should be, how should anyone else know ?

 

[James006]

because I am getting a value error..

Based on Steve's assumption of your expected result ... make use to adjust your separator ...

and probably replace commas by semi-colons ...

HTH

 

[steve the fish]

What we need to know is how in Austria do you write the date and time in excel?

 

[silentwolf]

Hi guys,

thanks for your reply.
I have changed substitute to wechsel that is what it means in german excel and replaced commas with semi colons.. but I am not sure what =+0 supose to mean and cant work it out.

The file is a csv file I download but gives me in excel not the time as in time 09:28:35 or what ever it may be.

In german the date is writen in dd/mm/yyyy ...30.12.2018 20:20:45

can not explain it better but hope you understand it what I am after?
Can not tell you what that number means as I can not see the time correctly

 

[Gerald Higgins]

Hi, it's not
=+0
it's
=0+
and this should not need to change because of the English / German translation.
One reason for using this is to force Excel to treat a number that has been entered as text, as a number.

With regard to the time format, I'm guessing that
2017-12-30-09.28.29.937036
means
30th December 2017, 09 hours 28 minutes 29 seconds and 0.937036 of a second.

Is this correct ?

If yes, then the next question is - how do you want this to be displayed ?

Note - I've change the second instance of "28" in your original, to "29", just to try and reduce confusion.

 

[silentwolf]

Hi,
Gerald
well I don t need miliseconds of the time to be displayed. So hours and minutes would be enough for what I need it.

yes 2017-12-30 means 30th of Dez. 2017

but the forumlar sitll gives me an error when I try to change this to german formula (