Covversion of date formats
 

Is there a way to make date formats read 1/1/2009 when you converty them to
mmm-yy, instead of them staying at their original format.

Basically, if a cell reads 3/3/2009 and I format it to read Mar-09, the
actual cell value still reads 3/3/2009. I want this to revert to 1/3/2009 to
ensure another worksheet can pick this up.

THe other worksheet in effect, only recognises dates formatted to the first
of each month before converting them to mmm-yy. If anything is different to
the first of each month, it does not recognise it.

Any help would be greatly appreciated.

Covversion of date formats
 

Hi,

When you change the format to mmm-yy, it is only the format which will
change, not the date itself. What you could do on the other sheet (which
only accept the first date of every month) is
=date(year(sheet1!A7),month(sheet1!A7),1). This will pick the year and
month from the date in the source sheet and will take the day as 1.

I have assumed that the cell sheet1!A7 holds 3/3/2009

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"The Message" wrote in message
...
Is there a way to make date formats read 1/1/2009 when you converty them
to
mmm-yy, instead of them staying at their original format.

Basically, if a cell reads 3/3/2009 and I format it to read Mar-09, the
actual cell value still reads 3/3/2009. I want this to revert to 1/3/2009
to
ensure another worksheet can pick this up.

THe other worksheet in effect, only recognises dates formatted to the
first
of each month before converting them to mmm-yy. If anything is different
to
the first of each month, it does not recognise it.

Any help would be greatly appreciated.

Covversion of date formats
 

It would be worth to have a look at the below link (by Chip Pearson) which
explains how excel stores date and time..

http://www.cpearson.com/excel/datetime.htm

If this post helps click Yes
---------------
Jacob Skaria


"The Message" wrote:

Is there a way to make date formats read 1/1/2009 when you converty them to
mmm-yy, instead of them staying at their original format.

Basically, if a cell reads 3/3/2009 and I format it to read Mar-09, the
actual cell value still reads 3/3/2009. I want this to revert to 1/3/2009 to
ensure another worksheet can pick this up.

THe other worksheet in effect, only recognises dates formatted to the first
of each month before converting them to mmm-yy. If anything is different to
the first of each month, it does not recognise it.

Any help would be greatly appreciated.