macro or formulas to lookup by dates

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
473
Office Version
  1. 2019
greeting to all

i have 14days(7days/week) schedule with over 1000 employees with various working start time and end time each day in sheet1
i am looking for a macro or formula to export first 7days to sheet2 and another 7days to sheet3
also i need 2 empty rows to sepearte each start time for easy reference

i have manually do it on week1

how can i make this happens?

here is my workbookDropbox

your guidance would greatly appreciate


test.xlsx
ABCDEFGHI
1
2
3Worker TypeLOBLocationPerson No.DateNameJOBStart TimeEnd Time
401/26/2025apple8:158:45
501/26/2025apple8:1517:45
601/26/2025apple8:1517:45
701/26/2025apple8:1517:45
801/26/2025apple8:1517:45
901/26/2025apple8:1517:45
1001/26/2025apple8:1517:45
1101/26/2025apple8:3018:00
1201/26/2025apple8:3018:00
1301/26/2025apple8:3018:00
1401/26/2025apple8:3018:00
1501/26/2025apple8:3018:00
1601/26/2025apple8:3018:00
1701/26/2025apple8:3018:00
1801/26/2025apple8:3018:00
1901/26/2025apple9:0018:30
2001/26/2025apple9:0018:30
2101/26/2025apple9:0018:30
2201/26/2025apple9:0018:30
2301/26/2025apple9:0018:30
2401/26/2025apple10:1515:30
2501/26/2025apple11:3021:00
2601/26/2025apple12:3022:00
2701/26/2025apple13:1522:45
2801/26/2025apple13:1522:45
2901/26/2025apple13:1522:45
3001/26/2025apple13:1522:45
3101/26/2025apple13:1522:45
3201/26/2025apple13:1522:45
3301/26/2025apple13:1522:45
3401/26/2025apple13:1522:45
3501/26/2025apple13:1522:45
3601/26/2025apple13:1522:45
3701/26/2025apple13:1522:45
3801/26/2025apple13:1522:45
3901/26/2025apple15:3022:15
4001/27/2025peter8:158:45
4101/27/2025peter8:1517:45
4201/27/2025peter8:1517:45
4301/27/2025peter8:1517:45
4401/27/2025peter8:1517:45
4501/27/2025peter8:1517:45
4601/27/2025peter8:1517:45
4701/27/2025peter8:3018:00
4801/27/2025peter8:3018:00
4901/27/2025peter8:3018:00
5001/27/2025peter8:3018:00
5101/27/2025peter8:3018:00
5201/27/2025peter8:3018:00
5301/27/2025peter8:3018:00
5401/27/2025peter8:3018:00
5501/27/2025peter9:0018:30
5601/27/2025peter9:0018:30
5701/27/2025peter9:0018:30
5801/27/2025peter9:0018:30
5901/27/2025peter9:0018:30
6001/27/2025peter10:1515:30
6101/27/2025peter11:3021:00
6201/27/2025peter12:3022:00
6301/27/2025peter13:1522:45
6401/27/2025peter13:1522:45
6501/27/2025peter13:1522:45
6601/27/2025peter13:1522:45
6701/27/2025peter13:1522:45
6801/27/2025peter13:1522:45
6901/27/2025peter13:1522:45
7001/27/2025peter13:1522:45
7101/27/2025peter13:1522:45
7201/27/2025peter13:1522:45
7301/27/2025peter13:1522:45
7401/27/2025peter13:1522:45
7501/27/2025peter15:3022:15
7601/28/2025gary8:158:45
7701/28/2025gary8:1517:45
7801/28/2025gary8:1517:45
7901/28/2025gary8:1517:45
8001/28/2025gary8:1517:45
8101/28/2025gary8:1517:45
8201/28/2025gary8:1517:45
8301/28/2025gary8:3018:00
8401/28/2025gary8:3018:00
8501/28/2025gary8:3018:00
8601/28/2025gary8:3018:00
8701/28/2025gary8:3018:00
8801/28/2025gary8:3018:00
8901/28/2025gary8:3018:00
9001/28/2025gary8:3018:00
9101/28/2025gary9:0018:30
9201/28/2025gary9:0018:30
9301/28/2025gary9:0018:30
9401/28/2025gary9:0018:30
9501/28/2025gary9:0018:30
9601/28/2025gary10:1515:30
9701/28/2025gary11:3021:00
9801/28/2025gary12:3022:00
9901/28/2025gary13:1522:45
10001/28/2025gary13:1522:45
10101/28/2025gary13:1522:45
10201/28/2025gary13:1522:45
10301/28/2025gary13:1522:45
10401/28/2025gary13:1522:45
10501/28/2025gary13:1522:45
10601/28/2025gary13:1522:45
10701/28/2025gary13:1522:45
10801/28/2025gary13:1522:45
10901/28/2025gary13:1522:45
11001/28/2025gary13:1522:45
11101/28/2025gary15:3022:15
list




test.xlsx
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
801/26/202501/27/202501/28/202501/29/202501/30/202501/31/202502/01/2025
98:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
108:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
118:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
128:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
138:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
148:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
158:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
168:00kelvin8:00mary8:00hugo
178:00kelvin8:00mary8:00hugo
188:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
198:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
208:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
218:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
228:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
238:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
248:30apple8:30peter8:30gary8:30tom8:00mary8:00hugo
258:30apple8:30peter8:30gary8:30tom8:00mary8:00hugo
269:00gary8:30kelvin8:00mary8:00hugo
279:00gary8:30kelvin8:00mary8:00hugo
289:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
299:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
309:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
319:00apple9:00peter9:00tom
329:00apple9:00peter9:00tom
3310:15gary9:45kelvin8:30mary8:30hugo
348:30mary8:30hugo
3510:15apple10:15peter10:15tom8:30mary8:30hugo
3611:30gary11:30kelvin8:30mary8:30hugo
378:30mary8:30hugo
3811:30apple11:30peter11:30tom8:30mary8:30hugo
3912:30gary12:30kelvin
4012:30kelvin
4112:30apple12:30peter12:30tom9:45mary9:45hugo
4213:15gary
4313:15gary13:15kelvin
4413:15apple13:15peter13:15gary13:15tom13:15kelvin11:00mary11:00hugo
4513:15apple13:15peter13:15gary13:15tom13:15kelvin11:00mary11:00hugo
4613:15apple13:15peter13:15gary13:15tom13:15kelvin
4713:15apple13:15peter13:15gary13:15tom13:15kelvin
4813:15apple13:15peter13:15gary13:15tom13:15kelvin11:30mary11:30hugo
4913:15apple13:15peter13:15gary13:15tom13:15kelvin
5013:15apple13:15peter13:15gary13:15tom13:15kelvin
5113:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5213:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5313:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5413:15apple13:15peter13:15tom13:15kelvin12:30mary12:30hugo
5513:15apple13:15peter13:15tom13:15kelvin
5615:30gary
5713:15mary13:15hugo
5815:30apple15:30peter15:30tom15:30kelvin13:15mary13:15hugo
5913:15mary13:15hugo
6013:15mary13:15hugo
6113:15mary13:15hugo
6213:15mary13:15hugo
6313:15mary13:15hugo
6413:15mary13:15hugo
6513:15mary13:15hugo
6613:15mary13:15hugo
6713:15mary13:15hugo
6813:15mary13:15hugo
6913:15mary13:15hugo
7013:15mary13:15hugo
7113:15mary13:15hugo
7213:15mary13:15hugo
7313:15mary13:15hugo
7413:15mary13:15hugo
7513:15mary13:15hugo
7613:15mary13:15hugo
7713:15mary13:15hugo
78
79
8015:30mary15:30hugo
81
82
week1




test.xlsx
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
802/02/202502/03/202502/04/202502/05/202502/06/202502/07/202502/08/2025
9
10
week2
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Try this:

VBA Code: 
Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
  
  aDate = a(1, 5):  aName = a(1, 6):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 5) Then
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 5)
    End If
    If aName <> a(i, 6) Or aTime <> a(i, 8) Then k = k + 2
    
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 6)
    aDate = a(i, 5): aName = a(i, 6): aTime = a(i, 8)
  Next
  
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

😇
 
Upvote 0
DanteAmor said:
Try this:

VBA Code: 
Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
 
  aDate = a(1, 5):  aName = a(1, 6):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 5) Then
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 5)
    End If
    If aName <> a(i, 6) Or aTime <> a(i, 8) Then k = k + 2
  
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 6)
    aDate = a(i, 5): aName = a(i, 6): aTime = a(i, 8)
  Next
 
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

😇
Click to expand...
thank you very much for your reply, DanteAmor

THIS IS REAL DOPE AND IMPECCABLE!!!!!

however:

1) the code can run exactly what i want but i found when i put this code to my workbook(obviously not only one employee per day)
it turns out sepearted every start time like below screenshot

test.xlsx
ABCDEFGHI
3Worker TypeLOBLocationPerson No.DateNameJOBStart TimeEnd Time
401/26/2025apple8:158:45
501/26/2025peter8:1517:45
601/26/2025apple8:1517:45
701/26/2025peter8:1517:45
801/26/2025apple8:1517:45
901/26/2025peter8:1517:45
1001/26/2025apple8:1517:45
1101/26/2025peter8:3018:00
1201/26/2025apple8:3018:00
1301/26/2025peter8:3018:00
1401/26/2025apple8:3018:00
1501/26/2025peter8:3018:00
1601/26/2025apple8:3018:00
list



test.xlsx
ABCDEFGHIJKLMNO
801/26/202501/27/202501/28/202501/29/202501/30/202501/31/202502/01/2025
98:15apple8:15apple8:15apple8:15apple8:00apple8:00apple8:00apple
10
11
128:15peter8:15peter8:15peter8:15peter8:00peter8:00peter8:00peter
13
14
158:15apple8:15apple8:15apple8:15apple8:00apple8:00apple8:00apple
16
17
188:15peter8:15peter8:15peter8:15peter8:00peter8:00peter8:00peter
19
20
218:15apple8:15apple8:15apple8:15apple8:00apple8:00apple8:00apple
22
23
248:15peter8:15peter8:15peter8:15peter45687.333peter8:00peter8:00peter
25
26
278:15apple45684.34apple8:15apple45686.344apple8:00apple8:00apple8:00apple
28
week1



2) my tech team swapped the 2 columns on col E and col F
col E - date > col F
col F - name > col E
can you show me how do i amend your code?

thank you very much for you guidance and support
 
Upvote 0
kelvin_9 said:
it turns out sepearted every start time
Click to expand...
This is solved if before running the macro, you sort your data by Date, Name and start time.


kelvin_9 said:
2) my tech team swapped the 2 columns on col E and col F
col E - date > col F
col F - name > col E
can you show me how do i amend your code?
Click to expand...

I marked the changes in blue:

Rich (BB code): 
Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
  
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 8) Then k = k + 2
    
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(i, 8)
  Next
  
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

😇
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,226,048
Messages
6,188,561
Members
453,484
Latest member
jlo1673

Share this page

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