formatting DATE in Excel

May 2, 2010 at 01:22:32
Specs: Windows 7
Hi I have a ton of standard dates that need to be trasformed from, for example "5/2/2009", into "20090502" format, please let me know if there a way to do that in Excel.

See More: formatting DATE in Excel

Report •


#1
May 2, 2010 at 03:53:36
Hi,

Assuming that the dates such as "5/2/2009" have been recognized as dates by Excel, and are not just text, then all you need to do is to re-format the relevant cells.

Select cell or cells, right-click, select 'Format Cells...'
In the format dialog box select the number tab
then select 'Custom' (at the bottom of the list)
In the 'Type:' box enter yyyyddmm (no quotation marks required)
click OK
If the cell does not change to 20090502
then post back, and include what it does show.

Regards


Report •

#2
May 3, 2010 at 00:25:10
thanks much that worked, although it seems like my data updates only when there is a manual input of data, such as "20090502" or May 02, 2009. Is there a way to create a formula that takes ""20090502" and spits JUST the numbers (in another cell), rather than Excel recognizing the date behind the "customized" date? Would be very helpful. Regards

Report •

#3
May 3, 2010 at 05:57:48
Hi,

Try this - date (either as 5/2/2009 or as 20090502) in cell A1:

=TEXT(YEAR(A1),"0000")&TEXT(DAY(A1),"00")&TEXT(MONTH(A1),"00")

The result will be text and not an Excel date.

Regards


Report •

Related Solutions

#4
May 5, 2010 at 07:08:50
Fantastic! Thanks much.

Report •

#5
May 6, 2010 at 01:25:12
You're welcome

Regards

Humar


Report •

#6
June 24, 2010 at 04:43:16
Hi

I am making one preventive tool for preventive tool for my DG sets. where I need the date of the same day when particular activity gets expired in some other sheet after reaching certain figure which could be anything but I am not to do it. As i am using today in the formula it is showing the date but it keps changing next day which is makin gme unsure of the exact date when that activity has expired.

Please help on this!!!


Report •


Ask Question