Date Formatting/adding slashes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

teresa - 11 Apr 2008 19:42 GMT

Hello,

I have a spreadsheet with data from a datebase that list dates with no
slashes. How can I add slashes?

Current format -7132007
Desired format- 7/13/2007

Thanks

Reply to this Message

Mike H - 11 Apr 2008 20:22 GMT

Hi,

With your date in A1 try this

=DATE(VALUE(RIGHT(A1,4)), VALUE(LEFT(A1,1)), VALUE(MID(A1,2,2)))

If you want to change from a formula back into a date then copy it
paste special
select values
OK

Mike

> Hello,
>
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Gord Dibben - 11 Apr 2008 23:56 GMT

You can't just "add the slashes".

You must first get Excel to recognize as a date.

Assume 7132007 is in A2 enter in B2

=DATE(VALUE(RIGHT(A2,4)), VALUE(LEFT(A2,1)), VALUE(MID(A2,2,2)))

Returns July 13, 2007.

Format to m/dd/yyyy

Gord Dibben MS Excel MVP

>Hello,
>
[quoted text clipped - 5 lines]
>
>Thanks

Reply to this Message

Shane Devenshire - 12 Apr 2008 05:41 GMT

Hi Teresa,

You can simplify the suggested approaches

=DATE(VALUE(RIGHT(A2,4)), VALUE(LEFT(A2,1)), VALUE(MID(A2,2,2)))

to the following:

=DATE(RIGHT(A1,4), LEFT(A1),MID(A1,2,2))

However, keep in mind that all these approaches have a problem - what does
this represent: 1112009 is this 11/1/2009 or 1/11/2009?

To make these approaches better is is preferable that the original dates are
entered 02012007 which would be 2/1/2007. If the dates are entered this way
you should modify the above formula to read:

=DATE(RIGHT(A1,4), LEFT(A1,2),MID(A1,2,2))

Cheers,
Shane Devenshire
Microsoft Excel MVP

> Hello,
>
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Tim Rush - 02 Jun 2008 13:01 GMT

how would I do this with VBA. I've tried the following:
dDate = Application.WorksheetFunction.Date(Mid(strDateTime, 1, 2),
Mid(strDateTime, 5, 2), Mid(strDateTime, 3, 2))

where stDateTime is 12 digit string in the format 0812011324112 (yymmddhhmmss)

I get an unsupported method error

> Hi Teresa,
>
[quoted text clipped - 28 lines]
> >
> > Thanks

Reply to this Message

Dave Peterson - 02 Jun 2008 15:02 GMT

VBA has it's own version of the =date() worksheet function.

Look for DateSerial in VBA's help.

dDate = Dateserial(Mid(strDateTime, 1, 2), _
Mid(strDateTime, 5, 2), _
Mid(strDateTime, 3, 2))

> how would I do this with VBA. I've tried the following:
> dDate = Application.WorksheetFunction.Date(Mid(strDateTime, 1, 2),
[quoted text clipped - 36 lines]
> > >
> > > Thanks

Signature

Dave Peterson

Reply to this Message

Tim Rush - 02 Jun 2008 16:30 GMT

Perfeect! Thankyou.

> VBA has it's own version of the =date() worksheet function.
>
[quoted text clipped - 44 lines]
> > > >
> > > > Thanks

Reply to this Message

Rate this thread: