How to? Date Function

[NervousFred]

I have a date in a table that is in the format 20080620. (Year Month Day all
together). Is it possbile to make execl convert this into a format I could
use in the DAY360 function?

Thanks!

 

[Jim Thomlinson]

With your date in cell A1
=DATE(VALUE(LEFT(A1, 4)), VALUE(MID(A1, 5, 2)), VALUE(RIGHT(A1, 2)))

 

[Ron Rosenfeld]

I have a date in a table that is in the format 20080620. (Year Month Day all
together). Is it possbile to make execl convert this into a format I could
use in the DAY360 function?

Thanks!

Data/Text to Columns and select YMD for the format of that column.
--ron

 

[NervousFred]

Ron-

How do I get to the menu to change that specific column's options in 07 excel?

 

[NervousFred]

This won't work becuase the table I am using is imported data.

 

[NervousFred]

Just relized more of waht you said....diregard my earlier message

 

[NervousFred]

Worked like a charm thanks!

With your date in cell A1
=DATE(VALUE(LEFT(A1, 4)), VALUE(MID(A1, 5, 2)), VALUE(RIGHT(A1, 2)))

 

[Gord Dibben]

Without formulas...................

Data>Text to Columns>Next>Next>Column data format>Date>YMD>Finish


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Ron-

How do I get to the menu to change that specific column's options in 07 excel?

If you have the default ribbon setup:

1. Select Data from the top menu bar.
2. Select "Text to Columns" -- just to the right of center, in the Data Tools
section.

That brings up the Convert Text to Columns wizard; Step 1 of 3
<Next> --> Step 2 of 3
be sure it is not splitting the column
<Next> --> Step 3 of 3
be sure your column is selected.
Column Data Format
select Date and then, from the drop-down, YMD
<Finish>



--ron