Hello All,

I have an issue with dates. I am trying to convert any date such as
11/27/2006, 3/14/2005, to essentially only the month and year. I have a
formula that will only work correctly if all dates are the first of the
month so I need to convert the above dates to this: 11/1/2006 and
3/1/2005. Is there a way to do this? Any help would be greatly
appreciated! Thanks

Try this:

=Date-DAY(Date)+1

A1 = 11/27/2006

=A1-DAY(A1)+1 = 11/1/2006

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello All,
>
> I have an issue with dates. I am trying to convert any date such as
> 11/27/2006, 3/14/2005, to essentially only the month and year. I have a
> formula that will only work correctly if all dates are the first of the
> month so I need to convert the above dates to this: 11/1/2006 and
> 3/1/2005. Is there a way to do this? Any help would be greatly
> appreciated! Thanks
>

Hi

If all you want from your dates is month and year, and assuming the
dates are in column A,
then either use a column alongside your dates with the formula =A1 and
format the column
Format>Cells>Number>Custom> mmm-yy
or just format the column containing the dates in this manner.

If you want the result as Text (as opposed to still being stored
internally as the serial date number) then use
=TEXT(A1,"mmm-yy")

--
Regards

Roger Govier


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello All,
>
> I have an issue with dates. I am trying to convert any date such as
> 11/27/2006, 3/14/2005, to essentially only the month and year. I have
> a
> formula that will only work correctly if all dates are the first of
> the
> month so I need to convert the above dates to this: 11/1/2006 and
> 3/1/2005. Is there a way to do this? Any help would be greatly
> appreciated! Thanks
>

That formula is exactly what I needed. Thanks!


T. Valko wrote:
> Try this:
>
> =Date-DAY(Date)+1
>
> A1 = 11/27/2006
>
> =A1-DAY(A1)+1 = 11/1/2006
>
> Biff
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello All,
> >
> > I have an issue with dates. I am trying to convert any date such as
> > 11/27/2006, 3/14/2005, to essentially only the month and year. I have a
> > formula that will only work correctly if all dates are the first of the
> > month so I need to convert the above dates to this: 11/1/2006 and
> > 3/1/2005. Is there a way to do this? Any help would be greatly
> > appreciated! Thanks
> >

You're welcome. Thanks for the feedback!

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> That formula is exactly what I needed. Thanks!
>
>
> T. Valko wrote:
>> Try this:
>>
>> =Date-DAY(Date)+1
>>
>> A1 = 11/27/2006
>>
>> =A1-DAY(A1)+1 = 11/1/2006
>>
>> Biff
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hello All,
>> >
>> > I have an issue with dates. I am trying to convert any date such as
>> > 11/27/2006, 3/14/2005, to essentially only the month and year. I have a
>> > formula that will only work correctly if all dates are the first of the
>> > month so I need to convert the above dates to this: 11/1/2006 and
>> > 3/1/2005. Is there a way to do this? Any help would be greatly
>> > appreciated! Thanks
>> >
>