Date and Time format

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
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

[TABLE="width: 176"]
<colgroup><col width="176"></colgroup><tbody>[TR]
[TD="class: xl63, width: 176"]2017-12-30-09.28.28.937036

so it shows the date and time correctly?

Many thanks for your help!

Albert
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi,

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

What is your expected result ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
What we need to know is how in Austria do you write the date and time in excel?
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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 (
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top