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.

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.

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.