Display of Time Value In Cell

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am having an issue with how my time value cell contents are being displayed in my workbook.
I have a source data range in worksheet ws_lists. The values in columns in AM and AN represent the decimal equivalents of hours.

wloopkdata.xlsm
AKALAMAN
21[1] 7:00A - 3:00P0.2916670.625000
32[2] 3:00P - 11:00P0.6250000.958333
43[3] 11:00P - 7:00A0.9583330.291667
54[4] Other
LISTS


Using this code, I am trying to populate cells R3 and T3 with the values of iShifts and iShifte (declared as integers) respectively. Cells R3 and T3 are formatted as h:mm AM/PM. When I run my code, I am getting a value of 12:00 AM in both cells. If I click on R3, the formula box shows 12:00:00 AM, an in T3 1900-01-01 12:00:00 AM

Code:
Private Sub Worksheet_change(ByVal Target As Range)
     Dim sName As String
     Dim leNum As Long
     Dim lshift As Long
     Dim sShift As String
     Dim iShifts As Integer
     Dim iShifte As Integer
     
     If Not mbevents Then Exit Sub
     If Target.Address = "$M$2" Then
        mbevents = False
        sName = Target.Value
        MsgBox sName, , "NAME CHANGE"
        Unprotect
        With Range("M2").Font
            .Color = RGB(19, 65, 98)
            .Italic = False
        End With
        leNum = Application.WorksheetFunction.VLookup(sName, ws_lists.Range("A2:B50"), 2, False)
        lshift = Application.WorksheetFunction.VLookup(sName, ws_lists.Range("A2:C50"), 3, False)
        sShift = Application.WorksheetFunction.VLookup(lshift, ws_lists.Range("AK2:AN5"), 2, False)
        iShifts = Application.WorksheetFunction.VLookup(lshift, ws_lists.Range("AK2:AN5"), 3, False)
        iShifte = Application.WorksheetFunction.VLookup(lshift, ws_lists.Range("AK2:AN5"), 4, False)
        Range("T2") = leNum
        Range("M3") = sShift
        Range("R3") = iShifts
        Range("T3") = iShifte
        mbevents = True
    End If
End Sub

Anyone wish to point me in the right direction? I'm not sure where it's best to format numbers, at the source, or destination. For note, if lshift = 4, there will be no values for iShifts or iShifte. In that case, the user will be prompted to enter the times in cells R3 and T3, formatted as h:mm AM/PM.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Don't use integers for iShifts and iShifte. They will always be zero. Any integer will be midnight.
 
Upvote 0
Solution
Thank you JamesCanale. That was exactly the answer.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top