to separate date and time formula

[Soth]

Hi

How do you create a formula to separate date 04/12/2007 and time 00:00:00?

4/12/2007 00:00:00

Thank you,
Soth

 

[Rick Rothstein]

If A1 contains your combined date and time, then...

For the date part: =INT(A1)

For the time part: =MOD(A1,1)

You will probably have to format the cells you put these formulas in to
display the date and time properly.

 

[Soth]

Wonderful - Thanks Rick

If A1 contains your combined date and time, then...

For the date part: =INT(A1)

For the time part: =MOD(A1,1)

You will probably have to format the cells you put these formulas in to
display the date and time properly.

 

[Mithun Ghosh]

How to segregate year out of this data

1991mit

I require to show year in a cell and the remaining info in other cell





Rick Rothstein wrote:

If A1 contains your combined date and time, then...
02-Feb-09

If A1 contains your combined date and time, then..

For the date part: =INT(A1

For the time part: =MOD(A1,1

You will probably have to format the cells you put these formulas in to
display the date and time properly

--
Rick (MVP - Excel


Previous Posts In This Thread:

to separate date and time formula
H

How do you create a formula to separate date 04/12/2007 and time 00:00:00

4/12/2007 00:00:0

Thank you
Soth

If A1 contains your combined date and time, then...
If A1 contains your combined date and time, then..

For the date part: =INT(A1

For the time part: =MOD(A1,1

You will probably have to format the cells you put these formulas in to
display the date and time properly

--
Rick (MVP - Excel


Re: to separate date and time formula
Wonderful - Thanks Ric


:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel Identifying which formulas are slowing down workbook recalaculation
http://www.eggheadcafe.com/tutorial...are-slowing-down-workbook-recalaculation.aspx

 

[Pete_UK]

If your data is in A1 and comprises a four digit date followed by
other text, then you can do this:

B1: =LEFT(A1,4)*1 to get the year
C1: =RIGHT(A1,LEN(A1)-4) to get the other text

Copy down as far as required.

Hope this helps.

Pete

 

[vishal parihar]

Very simple

Excel Function or formula

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0,1,2,3,4,5,6,7,8,9")),LEN(A1))

Regards
Vishal Parihar
(e-mail address removed)

 

[Anand]

@Visal Parihar:

I tried to test this formula with 2010jjj , somehow it returns the
full string instead of the year part only.

Excel Function or formula

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0,1,2,3,4,5,6,7,8,9")),LEN(A1))

Regards,
Anand

 

[vishal parihar]

Use below formula

=TEXT(A1,"hh:mm:ss")