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.