Ashfaque
Search Beautiful Girls from your town for night
- Local time
- Tomorrow, 02:33
- Joined
- Sep 6, 2004
- Messages
- 897
Hi,
I have time-sheet data of our employees (punch in and out). I am transferring tbl data to Excel sheet using below VBA code. It works but not the same as I wish.
:banghead:
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\DelhiImport\Result\JTS-2016.xlsx")
Set objSht = objWkb.Worksheets("DELHI TIME-SHEET")
objSht.Range("A1:I1").Merge
objSht.Cells(1, 1).Value = "INTERNATIONAL AGENT GROUP"
'Font for the Title
objSht.Range("A1:A1").Font.Bold = True
objSht.Range("a1:a1").Font.Name = "Times New Roman"
objSht.Range("a1:a1").Font.Size = 12
objSht.Range("a1:a1").Font.Color = 16711680
objSht.Rows(1).RowHeight = 20
objSht.Cells(2, 1).Value = "DELHI BRANCH TIME SHEET" + "-" + Format(rst!Time, "MMM-YY")
objSht.Range("a2:I2").Merge
objSht.Cells(2, 1).HorizontalAlignment = xlCenter
objSht.Range("A2:A2").Font.Bold = True
objSht.Range("a2:a2").Font.Name = "Times New Roman"
objSht.Range("a2:a2").Font.Size = 10
objSht.Range("a2:a2").Font.Color = 0
objSht.Rows(2).RowHeight = 14
iRow = 5
Dim RowCount, PMCount As Double
RowCount = 1
PMCount = 0
Dim TempDate, TTempDate, TException
objSht.Cells(3, 1).Value = rst!Name & "-" & rst![ac-no]
objSht.Cells(3, 1).Font.Size = 7
objSht.Cells(3, 1).HorizontalAlignment = xlLeft
'
rst.MoveFirst
Do While Not rst.EOF
AMPM = Right(rst!Time, 2)
TAMPM = Right(rst!Time, 2)
TempDate = Left(rst!Time, 9)
TTempDate = Left(rst!Time, 9)
TException = rst!exception
'If AMPM <> "AM" Then GoTo CheckPM
'If TempDate = TTempDate And AMPM = "AM" Then
If TempDate = TTempDate And AMPM = "AM" And rst!exception = "Invalid" Then
PMCount = PMCount + 1
rst.MoveNext
ElseIf TempDate = TTempDate And AMPM = "AM" And rst!exception = "FOT" Then
TempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = Format(TempDate, "dd/mm/yyyy")
objSht.Cells(iRow, 1).HorizontalAlignment = xlRight
objSht.Cells(iRow, 1).Borders.Color = vbBlack
Dim Slash1, Slash2, Slash3, Slash4
Slash1 = Mid(rst!Time, 2, 1)
Slash2 = Mid(rst!Time, 4, 1)
Slash3 = Mid(rst!Time, 5, 1)
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 9, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash3 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate And AMPM = "AM" And rst!exception <> "FOT" Then
rst.MovePrevious
TempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = Format(TempDate, "dd/mm/yyyy")
objSht.Cells(iRow, 1).HorizontalAlignment = xlRight
objSht.Cells(iRow, 1).Borders.Color = vbBlack
Slash1 = Mid(rst!Time, 2, 1)
Slash2 = Mid(rst!Time, 4, 1)
Slash3 = Mid(rst!Time, 5, 1)
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 9, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash3 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate And AMPM = "PM" And rst!exception = "Invalid" Then
'iRow = iRow + 1
rst.MoveNext
'Loop
ElseIf TempDate = TTempDate And AMPM = "PM" And rst!exception = "FOT" Then
PMCount = 1
iRow = iRow - 1
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate Or AMPM = "AM" And rst![exception] <> "FOT" Then
iRow = iRow + 1
TempDate = Mid(rst!Time, 10, 5)
TTempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = TTempDate
objSht.Cells(iRow, 2).Value = TempDate
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
iRow = iRow + 1
RowCount = RowCount + 1
rst.MoveNext
Loop
further code.....
If you see the JPG of my tbl data, I have stucked somewhere in the code which is repeating one date...
Sometime,employee is punching out during morning office session without looking if the punching machine is set on IN or OUT. Hence many times there are more than one IN on the same date. Moreover sometime it error so "Invalid" records in the sheet.
Each day 2 records are correct. One is AM and other is PM with FOT remark at one date.
But sometime, there are 3 records on same date with 2 AM and 1 PM or 1 AM with 2 PM - indicating INvalid or FOT.
I suspect my loop is not properly handled by me.
What I want is :
1. if AM is with "Invalid" but no other record is available with FOT on same date, my code should collect this and devide its date and time factor in 2 cells.
2. If first record of the date is falling with PM then it should look for AM record for same date. And if there is no AM record at all in the same date then right OUT column of my excel sheet should be filled only and IN should be written with "No Puch" some thing like.
I managed to solve somehow but not fully working. Also I need shorten my vba code I have written with IF - END IF (if possible)
Please help.
Thanks in advance
I have time-sheet data of our employees (punch in and out). I am transferring tbl data to Excel sheet using below VBA code. It works but not the same as I wish.
:banghead:
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\DelhiImport\Result\JTS-2016.xlsx")
Set objSht = objWkb.Worksheets("DELHI TIME-SHEET")
objSht.Range("A1:I1").Merge
objSht.Cells(1, 1).Value = "INTERNATIONAL AGENT GROUP"
'Font for the Title
objSht.Range("A1:A1").Font.Bold = True
objSht.Range("a1:a1").Font.Name = "Times New Roman"
objSht.Range("a1:a1").Font.Size = 12
objSht.Range("a1:a1").Font.Color = 16711680
objSht.Rows(1).RowHeight = 20
objSht.Cells(2, 1).Value = "DELHI BRANCH TIME SHEET" + "-" + Format(rst!Time, "MMM-YY")
objSht.Range("a2:I2").Merge
objSht.Cells(2, 1).HorizontalAlignment = xlCenter
objSht.Range("A2:A2").Font.Bold = True
objSht.Range("a2:a2").Font.Name = "Times New Roman"
objSht.Range("a2:a2").Font.Size = 10
objSht.Range("a2:a2").Font.Color = 0
objSht.Rows(2).RowHeight = 14
iRow = 5
Dim RowCount, PMCount As Double
RowCount = 1
PMCount = 0
Dim TempDate, TTempDate, TException
objSht.Cells(3, 1).Value = rst!Name & "-" & rst![ac-no]
objSht.Cells(3, 1).Font.Size = 7
objSht.Cells(3, 1).HorizontalAlignment = xlLeft
'
rst.MoveFirst
Do While Not rst.EOF
AMPM = Right(rst!Time, 2)
TAMPM = Right(rst!Time, 2)
TempDate = Left(rst!Time, 9)
TTempDate = Left(rst!Time, 9)
TException = rst!exception
'If AMPM <> "AM" Then GoTo CheckPM
'If TempDate = TTempDate And AMPM = "AM" Then
If TempDate = TTempDate And AMPM = "AM" And rst!exception = "Invalid" Then
PMCount = PMCount + 1
rst.MoveNext
ElseIf TempDate = TTempDate And AMPM = "AM" And rst!exception = "FOT" Then
TempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = Format(TempDate, "dd/mm/yyyy")
objSht.Cells(iRow, 1).HorizontalAlignment = xlRight
objSht.Cells(iRow, 1).Borders.Color = vbBlack
Dim Slash1, Slash2, Slash3, Slash4
Slash1 = Mid(rst!Time, 2, 1)
Slash2 = Mid(rst!Time, 4, 1)
Slash3 = Mid(rst!Time, 5, 1)
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 9, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash3 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate And AMPM = "AM" And rst!exception <> "FOT" Then
rst.MovePrevious
TempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = Format(TempDate, "dd/mm/yyyy")
objSht.Cells(iRow, 1).HorizontalAlignment = xlRight
objSht.Cells(iRow, 1).Borders.Color = vbBlack
Slash1 = Mid(rst!Time, 2, 1)
Slash2 = Mid(rst!Time, 4, 1)
Slash3 = Mid(rst!Time, 5, 1)
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 9, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash3 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 2).Value = Format(TempDate, "hh:mm a")
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate And AMPM = "PM" And rst!exception = "Invalid" Then
'iRow = iRow + 1
rst.MoveNext
'Loop
ElseIf TempDate = TTempDate And AMPM = "PM" And rst!exception = "FOT" Then
PMCount = 1
iRow = iRow - 1
If Slash1 = "/" And Slash2 = "/" Then
TempDate = Mid(rst!Time, 10, 5)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "0" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "3" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "4" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "5" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "6" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "7" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "8" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "9" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
ElseIf Slash1 = "/" And Slash2 = "1" Then
TempDate = Mid(rst!Time, 10, 6)
objSht.Cells(iRow, 5).Value = Format(TempDate, "hh:mm p")
objSht.Cells(iRow, 5).HorizontalAlignment = xlCenter
End If
ElseIf TempDate = TTempDate Or AMPM = "AM" And rst![exception] <> "FOT" Then
iRow = iRow + 1
TempDate = Mid(rst!Time, 10, 5)
TTempDate = Left(rst!Time, 9)
objSht.Cells(iRow, 1).Value = TTempDate
objSht.Cells(iRow, 2).Value = TempDate
objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
End If
iRow = iRow + 1
RowCount = RowCount + 1
rst.MoveNext
Loop
further code.....
If you see the JPG of my tbl data, I have stucked somewhere in the code which is repeating one date...
Sometime,employee is punching out during morning office session without looking if the punching machine is set on IN or OUT. Hence many times there are more than one IN on the same date. Moreover sometime it error so "Invalid" records in the sheet.
Each day 2 records are correct. One is AM and other is PM with FOT remark at one date.
But sometime, there are 3 records on same date with 2 AM and 1 PM or 1 AM with 2 PM - indicating INvalid or FOT.
I suspect my loop is not properly handled by me.
What I want is :
1. if AM is with "Invalid" but no other record is available with FOT on same date, my code should collect this and devide its date and time factor in 2 cells.
2. If first record of the date is falling with PM then it should look for AM record for same date. And if there is no AM record at all in the same date then right OUT column of my excel sheet should be filled only and IN should be written with "No Puch" some thing like.
I managed to solve somehow but not fully working. Also I need shorten my vba code I have written with IF - END IF (if possible)
Please help.
Thanks in advance