Rounding to a month

[bam]

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]

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

will return the 1st of each month.

 

[bam]

Thanks very much!

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

will return the 1st of each month.

 

[zhaoning.zju]

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)