Date Formatting/adding slashes
 

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

Answer: Date Formatting/adding slashes
 

Hi there!

Adding slashes to dates in Excel is a quick and easy process. Here's how you can do it:

1. Select the column that contains the dates you want to format.
2. Right-click on the selected cells and choose "Format Cells" from the drop-down menu.
3. In the "Format Cells" dialog box, select "Custom" from the list of categories on the left.
4. In the "Type" field on the right, enter the following format code: "m/d/yyyy" (without the quotes).
5. Click "OK" to apply the new format to the selected cells.

That's it! Your dates should now be formatted with slashes in the desired format.

If you want to apply this format to a single cell, you can simply type the date in the desired format (e.g. 7/13/2007) and Excel will recognize it as a date and format it accordingly.

Date Formatting/adding slashes
 

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

"teresa" wrote:

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

Date Formatting/adding slashes
 

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

On Fri, 11 Apr 2008 11:42:00 -0700, teresa
wrote:

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

Date Formatting/adding slashes
 

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

"teresa" wrote in message
...
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

Date Formatting/adding slashes
 

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

"Shane Devenshire" wrote:

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

"teresa" wrote in message
...
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

Date Formatting/adding slashes
 

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))


Tim Rush wrote:

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

"Shane Devenshire" wrote:

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

"teresa" wrote in message
...
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



--

Dave Peterson

Date Formatting/adding slashes
 

Perfeect! Thankyou.

"Dave Peterson" wrote:

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))


Tim Rush wrote:

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

"Shane Devenshire" wrote:

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

"teresa" wrote in message
...
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



--

Dave Peterson