CALCULATE SHIFT HOURS FROM SHIFT WINDOW (START TIME AND END TIME)

Say, you are given shift timings ( shift start time and shift end time) and you need to calculate shift hours from them. Below is the formula for that.

Formula in B2:
=MOD(RIGHT(A2,5)-LEFT(A2,5),1)*24 and drag it down.

RESTRICT USER TO MOVE YOUR USERFORM

Use below codes to restrict the users to move your userform.

1) Declare variables

Private m_sngAnchorLeft As Single
Private m_sngAnchorTop As Single
Private m_blnSetAnchor As Boolean


2) Paste the below code in the Activate event of the userform

Private Sub UserForm_Activate()
If Me.Visible Then
        If Not m_blnSetAnchor Then
            m_sngAnchorLeft = Me.Left
            m_sngAnchorTop = Me.Top
            m_blnSetAnchor = True
        End If
    End If
End Sub

3) Paste the below code in the Deactivate event of the userform

Private Sub UserForm_Deactivate()
m_blnSetAnchor = False
End Sub

4) Paste the below code in the Layout event of the userform

Private Sub UserForm_Layout()
If m_blnSetAnchor Then
        Me.Left = m_sngAnchorLeft
        Me.Top = m_sngAnchorTop
    End If
End Sub

Note: All the above codes should be used

DISABLE CLOSE("X") BUTTON OF USERFORM

To disable the close(X) button of userform paste the below code on the QueryClose event of your userform.


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, please use a button on the form.", vbCritical
End If
End Sub

CREATE FUNNEL CHART USING FORMULA

Following are the Steps:

1. Sort your data in Descending Order
2. Enter formula in C2  =REPT("|",B2/50) and drag it down
3.Choose the color that you want.

CHANGE TIME FORMAT FROM "HH:MM Hrs" TO "HH:MM:SS"

Formula in B2:
=TEXT(LEFT(A2,2)/24+MID(A2,4,2)/1440,"hh:mm:ss") and drag it down.

FIND AVERAGE OF VALUES EXCLUDING MAX AND MIN VALUE

Formula in A13:

=(SUM(A1:A12)-MIN(A1:A12)-MAX(A1:A12))/(COUNT(A1:A12)-2)

FIND OUT MAXIMUM OCCURED TEXT IN A RANGE

Array formula in B2:
=INDEX($A$2:$A$9,MATCH(MAX(COUNTIF($A$2:$A$9,$A$2:$A$9)),COUNTIF($A$2:$A$9,$A$2:$A$9),0)) with CSE