Custom Format for User Defined Function
 

Hi,

I am making my first attempt at a custom function and am having some
trouble. I have two columns with dates with the format 8-Feb and
12-Jun as an example. The 8 and 12 are actually the year, but Excel is
recognizing it as the day. That's fine, because I just need it as a
display thing. What I want to do is create a format that will show me
"8-Feb - 12-Jun". When I do it A1&" - "&A2, I get the serial number of
the dates (the wrong dates because again, Excel reads the numbers as
the day and appends 2006 as the year).

So, I am trying to create a user function called Window. How would I
go about creating this user function so that it comes out in the right
format?

Or, can I simply make a custom format in the Format Cells screen that
will do the trick?

Thanks!

Brett

Custom Format for User Defined Function
 

By the way, I forgot to mention that the reason I'm using a UDF is
because when I made a custom format "d-mmm & d-mmm", I can only select
1 cell, so I wanted it to be like Window(Arg1,Arg2) and just pop it in
that format.

Thanks!


wrote:
Hi,

I am making my first attempt at a custom function and am having some
trouble. I have two columns with dates with the format 8-Feb and
12-Jun as an example. The 8 and 12 are actually the year, but Excel is
recognizing it as the day. That's fine, because I just need it as a
display thing. What I want to do is create a format that will show me
"8-Feb - 12-Jun". When I do it A1&" - "&A2, I get the serial number of
the dates (the wrong dates because again, Excel reads the numbers as
the day and appends 2006 as the year).

So, I am trying to create a user function called Window. How would I
go about creating this user function so that it comes out in the right
format?

Or, can I simply make a custom format in the Format Cells screen that
will do the trick?

Thanks!

Brett

Custom Format for User Defined Function
 

Try this pair of functions



Function Window(Cell1, Cell2)
Year1 = Day(Cell1)
Year2 = Day(Cell2)
Month1 = Month(Cell1)
Month2 = Month(Cell2)
Month1 = ConvertToNamedMonth(Month1)
Month2 = ConvertToNamedMonth(Month2)
Window = Year1 & "-" & Month1 & " - " & Year2 & "-" & Month2
End Function

Function ConvertToNamedMonth(MonthNumber)
Select Case MonthNumber
Case Is = 1
ConvertToNamedMonth = "Jan"
Case Is = 2
ConvertToNamedMonth = "Feb"
Case Is = 3
ConvertToNamedMonth = "Mar"
Case Is = 4
ConvertToNamedMonth = "Apr"
Case Is = 5
ConvertToNamedMonth = "May"
Case Is = 6
ConvertToNamedMonth = "Jun"
Case Is = 7
ConvertToNamedMonth = "Jul"
Case Is = 8
ConvertToNamedMonth = "Aug"
Case Is = 9
ConvertToNamedMonth = "Sep"
Case Is = 10
ConvertToNamedMonth = "Oct"
Case Is = 11
ConvertToNamedMonth = "Nov"
Case Is = 12
ConvertToNamedMonth = "Dec"
End Select
End Function


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=569198

Custom Format for User Defined Function
 

Awesome, thanks!


mrice wrote:
Try this pair of functions



Function Window(Cell1, Cell2)
Year1 = Day(Cell1)
Year2 = Day(Cell2)
Month1 = Month(Cell1)
Month2 = Month(Cell2)
Month1 = ConvertToNamedMonth(Month1)
Month2 = ConvertToNamedMonth(Month2)
Window = Year1 & "-" & Month1 & " - " & Year2 & "-" & Month2
End Function

Function ConvertToNamedMonth(MonthNumber)
Select Case MonthNumber
Case Is = 1
ConvertToNamedMonth = "Jan"
Case Is = 2
ConvertToNamedMonth = "Feb"
Case Is = 3
ConvertToNamedMonth = "Mar"
Case Is = 4
ConvertToNamedMonth = "Apr"
Case Is = 5
ConvertToNamedMonth = "May"
Case Is = 6
ConvertToNamedMonth = "Jun"
Case Is = 7
ConvertToNamedMonth = "Jul"
Case Is = 8
ConvertToNamedMonth = "Aug"
Case Is = 9
ConvertToNamedMonth = "Sep"
Case Is = 10
ConvertToNamedMonth = "Oct"
Case Is = 11
ConvertToNamedMonth = "Nov"
Case Is = 12
ConvertToNamedMonth = "Dec"
End Select
End Function


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=569198

Custom Format for User Defined Function
 

Try

=Range("A1").Text & " - " & Range("A2").Text



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
Hi,

I am making my first attempt at a custom function and am having some
trouble. I have two columns with dates with the format 8-Feb and
12-Jun as an example. The 8 and 12 are actually the year, but Excel is
recognizing it as the day. That's fine, because I just need it as a
display thing. What I want to do is create a format that will show me
"8-Feb - 12-Jun". When I do it A1&" - "&A2, I get the serial number of
the dates (the wrong dates because again, Excel reads the numbers as
the day and appends 2006 as the year).

So, I am trying to create a user function called Window. How would I
go about creating this user function so that it comes out in the right
format?

Or, can I simply make a custom format in the Format Cells screen that
will do the trick?

Thanks!

Brett

Custom Format for User Defined Function
 

without the =

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Try

=Range("A1").Text & " - " & Range("A2").Text



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
Hi,

I am making my first attempt at a custom function and am having some
trouble. I have two columns with dates with the format 8-Feb and
12-Jun as an example. The 8 and 12 are actually the year, but Excel is
recognizing it as the day. That's fine, because I just need it as a
display thing. What I want to do is create a format that will show me
"8-Feb - 12-Jun". When I do it A1&" - "&A2, I get the serial number of
the dates (the wrong dates because again, Excel reads the numbers as
the day and appends 2006 as the year).

So, I am trying to create a user function called Window. How would I
go about creating this user function so that it comes out in the right
format?

Or, can I simply make a custom format in the Format Cells screen that
will do the trick?

Thanks!

Brett