PDA

View Full Version : Sleeper: How to create table of missing items?



marshybid
04-23-2008, 07:13 AM
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 :yes

Bob Phillips
04-23-2008, 07:22 AM
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
04-23-2008, 07:29 AM
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
04-23-2008, 07:42 AM
Well mine reads UK, so I don't think I can help.

marshybid
04-23-2008, 07:48 AM
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
04-23-2008, 07:53 AM
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
04-23-2008, 08:11 AM
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
04-23-2008, 10:39 AM
Don't know why they come out that way, but I've never been able to change them. Thanks anyway :o)

Simon Lloyd
04-23-2008, 12:35 PM
Maybe they are not true dates!, perhaps formatted as text or such like.

P.S i havent looked at the workbook

marshybid
04-24-2008, 04:24 AM
Hello

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

Hopefully that will help.

Thanks,

Marshybid

Bob Phillips
04-24-2008, 04:29 AM
If you do, I will commit to giving you a solution.

marshybid
04-24-2008, 04:38 AM
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
04-24-2008, 08:12 PM
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
04-24-2008, 08:12 PM
These two sheets are actually in two separate workbooks?

lucas
04-24-2008, 08:46 PM
Duplicates etc. deleted. It's happened to me before......

marshybid
04-25-2008, 08:17 AM
Amazing!!!!

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
04-25-2008, 08:42 AM
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
04-25-2008, 02:21 PM
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
04-26-2008, 12:43 PM
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
04-26-2008, 12:47 PM
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

marshybid
04-28-2008, 02:34 PM
Hi All,

Any additional support on this gratefully received.

Thanks,

Marshybid

Bob Phillips
04-28-2008, 03:01 PM
tomorrow ...

marshybid
04-29-2008, 02:08 AM
Thanks xld.

I hope my answers have been clear enough to answer your queries.

Marshybid