UDF multiplier help

[Rift_tide]

Hi I'm new to building custom functions, and am currently trying to build one that can calculate total numbers of days before a certain date that an employee would start.
The format that dates are given in is something like "7.2" which would mean 7 weeks and 1 day making it 36 days total which is the result I'm trying to get. The whole number which varies would always be multiplied by 5, and the decimal would always either be .2, .4, .6, .8, or 0.

I'm been able to get the decimal section to work but haven't been able to figure out how to get the whole number to work as well as calculate the decimal at the same time. Any suggestions or ideas would be appreciated.

Function PrepWkCalc(CellRef)

Select Case CellRef

Case Is = 0.2
PrepWkCalc = 1
Case Is = 0.4
PrepWkCalc = 2
Case Is = 0.6
PrepWkCalc = 3
Case Is = 0.8
PrepWkCalc = 4

End Select

End Function

 

[Joe4]

Welcome to the Board!

Maybe something like this:

Function PrepWkCalc(CellRef)

    Dim d As Double
   
    'Find decimal portion of number
    d = Round(CellRef - Int(CellRef), 1)

    Select Case d
        Case 0.2
            PrepWkCalc = 1
        Case 0.4
            PrepWkCalc = 2
        Case 0.6
            PrepWkCalc = 3
        Case 0.8
            PrepWkCalc = 4
    End Select

End Function

 

[Rift_tide]

Welcome to the Board!

Maybe something like this:

Function PrepWkCalc(CellRef)

    Dim d As Double
  
    'Find decimal portion of number
    d = Round(CellRef - Int(CellRef), 1)

    Select Case d
        Case 0.2
            PrepWkCalc = 1
        Case 0.4
            PrepWkCalc = 2
        Case 0.6
            PrepWkCalc = 3
        Case 0.8
            PrepWkCalc = 4
    End Select

End Function

That works perfect for the decimal portion! Can you think of any way I can multiply the whole number by 5 at the same time? Or would that need to be a separate function?

 

[Joe4]

Here is a simplified version that should do what you want:

Function PrepWkCalc(CellRef)

    Dim w As Long
    Dim d As Double
   
    'Get whole number portion and multiply by 5
    w = Int(CellRef) * 5
   
    'Find decimal portion of number
    d = Round(CellRef - Int(CellRef), 1)

    Select Case d
        Case 0, 0.2, 0.4, 0.6, 0.8
            PrepWkCalc = w + (d * 5)
    End Select

End Function

 

[Rift_tide]

Here is a simplified version that should do what you want:

Function PrepWkCalc(CellRef)

    Dim w As Long
    Dim d As Double
  
    'Get whole number portion and multiply by 5
    w = Int(CellRef) * 5
  
    'Find decimal portion of number
    d = Round(CellRef - Int(CellRef), 1)

    Select Case d
        Case 0, 0.2, 0.4, 0.6, 0.8
            PrepWkCalc = w + (d * 5)
    End Select

End Function

Thank you Joe, I just tested that out and it works perfectly!

 

[Joe4]

You are welcome.
Glad I was able to help.