Date and time format

[madocar]

Hi guys,

could u help me with converting this

04.10.2013 08:00

to date&time format. Because 04.10.2013 08:00 is just text format and I need to convert it to date and time to work with it.

 

[Faseeh]

Hi madocar,

Try this in D4, and format cells so that it shows both date and time:

=DATE(MID(B4,7,4),MID(B4,4,2),LEFT(B4,2))+RIGHT(B4,5)

 

[Debraj]

One more approach.. try this..

=SUBSTITUTE(B4,".","-")+0

 

[NARAYANK991]

Hi ,

What ever formula you use , please ensure that the cell format is temporarily changed to :

mmmm-dd hh:mm

to verify whether the formula results in the correct date.

9/11 can be interpreted as September 11 or November 9 ; only you can decide which of them is the correct date.

Narayan

 

[magbo]

Hi,
I would use "Text to columns" twice.
First - with space as separator, which divides your string into two columns: date (still string) and time (already converted into numbers).
Second - with setting that converted values are supposed to be Date in order DMY or MDY (it depends on your input data).
Finally - if you want to keep data in one column - just sum date and time.

Magda

 

[madocar]

Thanks all of u

@Debraj

=SUBSTITUTE(B4,".","-")+0 this formula is very practical

 

[G Srinivas Rao]

Hai,
You can sepearate the Date and time in two different columns by using the "Faseeh" formula

Date = DATE(MID(B4,7,4),MID(B4,4,2),LEFT(B4,2))
 
Time = RIGHT(B4,5)