Converting Date to Beginning of the Month

[carlsondaniel]

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

 

[T. Valko]

Try this:

=Date-DAY(Date)+1

A1 = 11/27/2006

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

Biff

 

[Roger Govier]

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")

 

[carlsondaniel]

That formula is exactly what I needed. Thanks!

Try this:

=Date-DAY(Date)+1

A1 = 11/27/2006

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

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

That formula is exactly what I needed. Thanks!