Rounding to a month

Hello, I have a list of 1000 items with dates over the last two years. Many
duplicates, etc. I need a formula that transforms the dates to a single date
with the month and year. To be more clear ... I am looking for the following
...
1/02/05 = 1/1/05
1/23/05 = 1/1/05
1/31/05 = 1/1/05
3/15/06 = 3/1/06
3/16/06 = 3/1/06
5/25/04 = 5/1/04

Thanks in advance

bmac

Dave
Mon Mar 27 19:17:09 CST 2006

=date(year(a1),month(a1),1)

will return the 1st of each month.

bam wrote:
>
> Hello, I have a list of 1000 items with dates over the last two years. Many
> duplicates, etc. I need a formula that transforms the dates to a single date
> with the month and year. To be more clear ... I am looking for the following
> ...
> 1/02/05 = 1/1/05
> 1/23/05 = 1/1/05
> 1/31/05 = 1/1/05
> 3/15/06 = 3/1/06
> 3/16/06 = 3/1/06
> 5/25/04 = 5/1/04
>
> Thanks in advance
>
> bmac

--

Dave Peterson

bam
Mon Mar 27 19:48:27 CST 2006

Thanks very much!

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44288E95.667DCB86@verizonXSPAM.net...
> =date(year(a1),month(a1),1)
>
> will return the 1st of each month.
>
> bam wrote:
>>
>> Hello, I have a list of 1000 items with dates over the last two years.
>> Many
>> duplicates, etc. I need a formula that transforms the dates to a single
>> date
>> with the month and year. To be more clear ... I am looking for the
>> following
>> ...
>> 1/02/05 = 1/1/05
>> 1/23/05 = 1/1/05
>> 1/31/05 = 1/1/05
>> 3/15/06 = 3/1/06
>> 3/16/06 = 3/1/06
>> 5/25/04 = 5/1/04
>>
>> Thanks in advance
>>
>> bmac
>
> --
>
> Dave Peterson

zhaoning
Mon Mar 27 21:37:23 CST 2006

First, write a UDF as follows:
' Begin
Function scan(rg As Range, n As Integer, dlm As String) As String
Application.Volatile
Dim allFields As Variant
allFields = Split(rg.Text, dlm)
scan = allFields(n - 1)
End Function
' End

Then, use formula in the worksheet:
=scan(A1,1,"/")&"/1/"&scan(A1,3,"/")
In this case, the result is of string type, if you want the result to
be of date type, write the formula like this:
=date(scan(A1,3,"/"),scan(A1,1,"/"),1)