Converting Date to Beginning of the Month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carlsondaniel@gmail.com - 29 Nov 2006 22:52 GMT

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

Reply to this Message

T. Valko - 29 Nov 2006 22:59 GMT

Try this:

=Date-DAY(Date)+1

A1 = 11/27/2006

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

Biff

> Hello All,
>
[quoted text clipped - 4 lines]
> 3/1/2005. Is there a way to do this? Any help would be greatly
> appreciated! Thanks

Reply to this Message

carlsondaniel@gmail.com - 30 Nov 2006 20:16 GMT

That formula is exactly what I needed. Thanks!

> Try this:
>
[quoted text clipped - 14 lines]
> > 3/1/2005. Is there a way to do this? Any help would be greatly
> > appreciated! Thanks

Reply to this Message

T. Valko - 01 Dec 2006 02:52 GMT

You're welcome. Thanks for the feedback!

Biff

> That formula is exactly what I needed. Thanks!
>
[quoted text clipped - 16 lines]
>> > 3/1/2005. Is there a way to do this? Any help would be greatly
>> > appreciated! Thanks

Reply to this Message

Roger Govier - 29 Nov 2006 23:51 GMT

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

Signature

Regards

Roger Govier

> Hello All,
>
[quoted text clipped - 6 lines]
> 3/1/2005. Is there a way to do this? Any help would be greatly
> appreciated! Thanks

Reply to this Message

Rate this thread: