Excel VBA Custom Function

[Valerie]

Hello. I am trying to create a custom function to convert an SAP life format
into total number of months where the reference in the function is to the one
cell. SAP cell is formatted as text and shows as "000/000" where the first
set of zeros represents years and second set represents months. I want this
converted to total number of months. Say I have 006/003 - this would be 6
yrs 3 mos for a total of 75 months. I need the number 75. I have done this
with a formula:

=Value(Left(A2, 3) * 12) + Value(Right(A2, 3))

but this is cumbersome every time I want to do this. I would like a
function where I can select the cell needing conversion and the function
takes care of the rest. Can this be done as a custom function?

Thanks in advance!
Valerie

 

[Bernie Deitrick]

Valerie,

You can either use VBA, or a named formula.

For the named formula, with your string in A2, select cell B2, and use Insert / Name / Define....
and type MSAP in the top box, and in the "Refers to:" box use

=Value(Left(A2, 3) * 12) + Value(Right(A2, 3))

Then in any cell to the right of a SAP life format string, enter

=MSAP

and Excel will use the values in the cell to the left to calculate the months.


For VBA, use this in a regular codemodule

Function MSAP(myR As Range) As Integer
MSAP = Val(Left(myR.Value, 3) * 12) + Val(Right(myR.Value, 3))
End Function

and use it like

=MSAP(A2)

HTH,
Bernie
MS Excel MVP

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

A1 = 006/3
=CMONTH(A1)

Function CMONTH(varRange As Range) As Long
CMONTH = Split(varRange.Text, "/")(0) * 12 + Split(varRange.Text, "/")(1)
End Function

If this post helps click Yes

 

[Per Jessen]

Hi Valerie

Try this UDF:

Public Function SAPMonth(SAPstring As String) As Long
SAPMonth = Left(SAPstring, 3) * 12 + Right(SAPstring, 3)
End Function

Syntax:
=SAPMonh(A2)

BTW: Your formula can be reduced to this:
=LEFT(A2,3)*12+RIGHT(A2)

Regards,
Per

 

[Valerie]

Perfect! Just what I was looking for! Thanks!

Valerie,

You can either use VBA, or a named formula.

For the named formula, with your string in A2, select cell B2, and use Insert / Name / Define....
and type MSAP in the top box, and in the "Refers to:" box use

=Value(Left(A2, 3) * 12) + Value(Right(A2, 3))

Then in any cell to the right of a SAP life format string, enter

=MSAP

and Excel will use the values in the cell to the left to calculate the months.


For VBA, use this in a regular codemodule

Function MSAP(myR As Range) As Integer
MSAP = Val(Left(myR.Value, 3) * 12) + Val(Right(myR.Value, 3))
End Function

and use it like

=MSAP(A2)

HTH,
Bernie
MS Excel MVP