Sleeper: How to create table of missing items?

[marshybid]

Hi All,

I would be really grateful for some assistance please.

I have 2 seperate spreadsheets of data (I have pasted them into 2 worksheets in the attached zip file)

I need to do the following;

For all User ID's (column D in Contingent Staff) I need to calculate how many timesheets I should have received to date (date being date when report is run) based on how many weeks between start date (column F Contingent Staff) and end date (column G Contingent Staff) this will be based on week ending dates (Sunday being the week end date) (example, timesheets for period 18 February to 2 March 2008 = 1 week (week ending 24 Feb) and 1 week (week ending 2 March)

Once I have this information I need to look up the User ID (column D Contingent Staff) in User ID (column H Timesheet Detail) If I do not find a timesheet for the week ending period then create a table of all missing timesheets showing User ID, ID (column B Timesheet Detail), X-Ref PO ID (column D Timesheet Detail), Contingent Staff First Name (column I Timesheet Detail), Contingent Staff Last Name (column K Timesheet Detail), Timesheet missing for Week Ending Date.

I have attached an example of the data, I really hope the above makes sense.

Thanks in advance,

Marshybid

[Bob Phillips]

Two problems.

Even though you are UK, your dates are not dates in that they are not UK formed, so a date of 03/27/2008 is nonsense to UK Excel.

You have future dates, for which there cannot be any timesheets yet.

[marshybid]

Hi there,

You are correct,the date formats are US. This is genrated from the system providing the data and can not be changed. My version of excel is set to read these correctly.

As per my original question the first part of the process is to identify all week ending dates for timesheets from Sart date to End date, but to only look for timesheets up to the date the report is being run.

Sorry it wasn't very clear.

Marshybid

[Bob Phillips]

Well mine reads UK, so I don't think I can help.

[marshybid]

Hi there,

If you were to go to Control Panel and select Regional and Language Options then select English (United States) as Region. This will cause excel to read US format dates, then it can simply be changed back again.

I appreciate your help.

Thanks,

Marshybid

[marshybid]

Just thought, could you not just replace the existing dates for you with UK format dates, then the macro would work the same on my US formatting. Wouldn't it?? Showing what a novice I am :o)

[Bob Phillips]

Just thought, could you not just replace the existing dates for you with UK format dates, then the macro would work the same on my US formatting. Wouldn't it?? Showing what a novice I am :o)

Yes, but I would have to change every one as they are all backwards, and that is beyond the call IMO.

BTW, whenever I get a workbook from the States, the dates are fine, Excel handles the local setting, so how come yours are messed up?

[marshybid]

Don't know why they come out that way, but I've never been able to change them. Thanks anyway :o)

[Simon Lloyd]

Maybe they are not true dates!, perhaps formatted as text or such like.

P.S i havent looked at the workbook

[marshybid]

Hello

I will try to get a copy of the data with UK formatted dates.

Hopefully that will help.

Thanks,

Marshybid

[Bob Phillips]

If you do, I will commit to giving you a solution.

[marshybid]

Thanks a lot

Your assistence is very much appreciated.

I hope to be able to post the amended spreadsheet by end of day today or tomorrow am.

[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

[rbrhodes]

These two sheets are actually in two separate workbooks?

[lucas]

Duplicates etc. deleted. It's happened to me before......

[marshybid]

[sign]Amazing!!!![/sign]

rbrhodes That is absolutely brilliant. It does exactly what it says on the tin :o)

OK, so now that I can use the macro above to convert the appropriate columns into date format, and put them into UK format.....

xld, will you be able to have a look at this data now and help with the original query???

Thanks everyone, you guys rock..

[marshybid]

Hi All,

I have attached a new version of the spreadsheet with dates in correct format.

Thanks to rbrhodes for his script to resolve this.

xld, hope this is better.

Marshybid

[Bob Phillips]

Here's a first shot, but a couple of things are eluding me?

Lots of the details from Timesheet Detail, but which row should I take if there are many entries for a user, wat do I do if there are none?

Public Sub ProcessData()
Const ANOMALIES As String = "Anomalies"
Dim sh As Worksheet
Dim target As Worksheet
Dim aryDates As Variant
Dim dte As Date
Dim NoUser As Boolean
Dim sFormula As String
Dim EvalFormula As String
Dim LastRow As Long
Dim i As Long
Dim j As Long
Set sh = Worksheets("Timesheet Detail")
    On Error Resume Next
    Set target = Worksheets(ANOMALIES)
    On Error GoTo 0
    If target Is Nothing Then
    Set target = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    target.Name = ANOMALIES
    End If
    target.Cells.ClearContents
With Worksheets("Contingent Staff")
    LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    sFormula = "INDEX(W2:W" & LastRow & ",0,MATCH(<test>,H2:H" & LastRow & ",0))"
    For i = 2 To LastRow
        dte = .Cells(i, "F").Value
        dte = dte + (7 - Weekday(dte, 2))
        EvalFormula = Replace(sFormula, "<test>", .Cells(i, "D").Value)
        aryDates = Application.Transpose(Application.Evaluate(EvalFormula))
        If Not ArrayIsAllocated(aryDates) Then
            Do
                OutputDetails Worksheets("Contingent Staff"), i, target, dte
                dte = dte + 7
            Loop Until dte >= .Cells(i, "G").Value + 7
        Else
            Do
                For j = LBound(aryDates) To UBound(aryDates)
                    If dte = aryDates(j) + (7 - Weekday(aryDates(j), 2)) Then
                        Exit For
                    End If
                Next j
                If j > UBound(aryDates) Then
                    OutputDetails Worksheets("Contingent Staff"), i, target, dte
                End If
                dte = dte + 7
            Loop Until dte >= .Cells(i, "G").Value + 7
        End If
    Next i
    End With
End Sub

Private Sub OutputDetails(ByRef sh As Worksheet, ByVal SourceRow As Long, _
                          ByRef target As Worksheet, WeDate As Date)
Static NextRow As Long
With sh
    NextRow = NextRow + 1
    target.Cells(NextRow, "A").Value = .Cells(SourceRow, "D").Value
    target.Cells(NextRow, "B").Value = "??"
    target.Cells(NextRow, "C").Value = "??"
    target.Cells(NextRow, "D").Value = "??"
    target.Cells(NextRow, "E").Value = "??"
    target.Cells(NextRow, "F").Value = WeDate
    End With
End Sub

Private Function ArrayIsAllocated(Arr As Variant) As Boolean
    On Error Resume Next
    ArrayIsAllocated = Not (IsError(LBound(Arr))) And _
        IsArray(Arr) And (LBound(Arr) <= UBound(Arr))
End Function

[marshybid]

Hi xld,

As a first draft this looks really good.

In answer to your query, the contingent staff worksheet is a total list of all contingent staff whose status is active (i.e. working now) the Timesheet Detail worksheet shows all timesheets submitted. The Timesheet detail sheet will have multiple rows per user ID as each row reporesents one days timesheet. I will add a column ("Y") in Timesheet Detail which will show the wedate for each timesheet submitted (attached updated spreadsheet).

The idea of the report is to identify every Contingent Staff and calculate which timesheets should be present based on the contingent staff start and end date (Contingent Staff worksheet) Then cross reference this with the Timesheet Detail worksheet and only show One reference per contingent satff (user ID) with all instances of missing timesheets by wedate (perhaps in a pivot table format). The trick I guess is getting the script to only look for submiytted timesheets up the last week ending date based on the date you run the report (i.e. if I run it on Monday next week - 28 April 2008 - the script should only show timesheets missing up to week ending 27 April 2008)

I hope this helps you.

I have run the script you provided and it looks good.

Your help is greatly appreciated.

Thanks a Lot

Marshybid

[marshybid]

Hi xld,

I have attached the updated sapreadsheet with week ending date for each timesheet in column Y in Timesheet Detail worksheet

Hope this helps.

Marshybid