Sleeper: How to create table of missing items?

[rbrhodes]

Hi,

Excel stores Dates and times as numbers. The only actual difference in UK/US dtaes is the formatting for _display_ ie:

'US formatted dates
Selection.NumberFormat = "m/d/yyyy"

' UK formatted dates
Selection.NumberFormat = "dd/mm/yyyy;@"

So if the dates don't work it means they aren't dates. Try selecting Cols F & G in Contigent staff sheet and format the cells as 'date'. More than 1/2 don't change!

You could put a formula in a helper column to change the displayed numbers in to dates...

or run this code:

Option Explicit
Sub ConvertToDate()
Dim cel As Range
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Workbooks("Help Cont Staff.xls").Sheets("Contingent Staff")
    Set ws2 = Workbooks("Help Time.xls").Sheets("Timesheet Detail")
'//First ws
    With ws1
    'Get last row of dates
    LastRow = .Range("F65536").End(xlUp).Row
    'Create Range reference to dates
    Set rng = .Range(Cells(2, 6).Address, Cells(LastRow, 7).Address)
    For Each cel In rng
        cel = DateSerial(Year(cel), Month(cel), Day(cel))
    Next cel
    End With
'//Second ws
    With ws2
    'Get last row of dates
    LastRow = .Range("V65536").End(xlUp).Row
    'Create Range reference to dates
    Set rng2 = .Range(Cells(2, 22).Address, Cells(LastRow, 24).Address)
    For Each cel In rng2
        cel = DateSerial(Year(cel), Month(cel), Day(cel))
    Next cel
    End With
    'Disregard invalid entry
    On Error Resume Next
'Ask User
    LastRow = InputBox("Enter 1 for US, 2 for UK formatted dates", "Default  = UK")
'Default is UK
    If LastRow = 1 Then
    'US formatted dates
    rng.NumberFormat = "m/d/yyyy"
    rng2.NumberFormat = "m/d/yyyy"
    Else
    ' UK formatted dates
    rng.NumberFormat = "dd/mm/yyyy;@"
    rng2.NumberFormat = "dd/mm/yyyy;@"
    End If
    'Delete objects
    Set cel = Nothing
    Set rng = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub