Punching TimeSolved/Closed

Question

Hi again!

I have this task ofcalculating the punching time of our agents. I have 4 sheets in one excel.
"Deliveroo Data" This is where the orders are coming from, "Order Details" and "Delivery Data" tabs are coming from our system, and "Data" where we would put in all the details needed . So basically, in order to get the punch time, We'll have to put the "time submitted" from the "deliveroo data" tab and the "order time" from the "Delivery data" tab in the "DATA" tab and subtract it and add the "order processing time" from the "delivery data" tab to get the PUNCHING TIME. I hope I was clear with my query, if you need more clarification please inform me. I will attach the excel file as well Im not sure how to upload ithere but i will send it via email

luigidelmonte · Answer

Please help me with this anyone?

TrowaD · Answer

Hi Luigi,

Unless I'm missing something, this could be done with 3 times the VLOOKUP function in 1 formula. 

If you have trouble applying it, then having a look at your file would definitely help. You can do so using a free file sharing site like http://www.ge.tt. Upload your file (always be carefull with sensitive data) and post back the download link here.

Best regards,
Trowa

TrowaD · Answer

Hi Luigi,

I'm unable to download your file, not sure why. I've uploaded files myself to test, but those files I can download just fine. Maybe it's the file size, maybe the site, maybe combination.

Can you decrease the file size by deleting tabs that are irrelevant to your query?

Sorry about this.

Best regards,
Trowa

luigidelmonte · Answer

I uploaded it in google drive. Here: https://drive.google.com/file/d/1pHueOhdlYL78fn5IuSXhDU-mdIl0BYRe/view?usp=drive_open

Thanks,
Luigi

TrowaD · Answer

Hi Luigi,

Great that worked!

Now I have 2 questions:

1. What connects the rows from the Deliveroo Data tab to the rows of the Delivery Data tab? I'm looking for a unique value on both sheets, so I know which rows of data go together. Like the order number for example, but Deliveroo order number is like ####, while the Delivery order number is like ###-#####.

2. Your order processing time isn't a time but a number. How do I need to interpret that number? When i says 0,88, does that mean the processing time is 0,88*60 = 53 minutes?

Best regards,
Trowa

luigidelmonte · Answer

Hi Trowad,

Sorry for the delayed response. I was handling too many reports.

to answer your questions:

1. The order number from deliveroo data tab ####, you can connect it with the order details tab and then you can match the order number ###-#####from the order details tab to the delivery data tab. That's how you can match them. The problem is, there are multiple items with the same order number in deliveroo data tab.

2. with the order processing time, if for example it's 0.88 it means it was punched 88 seconds.

TrowaD · Answer

Hi Luigi,

No problem, we both have other things to do :).

1. There needs to be a way to link the rows together. Which data need to be looked at (with the least amount of data as possible) to indentify each row as unique? 
For example: When I look at order 4439, there are 3 of them, but they all have different restaurants. Linking the short order number to the restaurant would make it unique, to find that rows long order number to be found on the Delivery tab.
But I'm not sure if that is the case for all order numbers.

2. Ok.

3. Where (Sheet and column) do you actually want the punching time to be placed?

Best regards,
Trowa

luigidelmonte · Answer

Thanks Trowad,

1. The Order ID, restaurant and the location/branch would make it match.

We can place the punching time on the "data" tab.

luigidelmonte · Answer

we should add a new column on the last part

TrowaD · Answer

Hi Luigi,

You make it like a real puzzle. I couldn't find the location, but it seems that the Restaurant Name also contains the location.

So Restaurant Name on Deliveroo sheet = Kitchen and Brand columns on the Order Details sheet.

For someone not familiar with your data, it is quite confusing.

I'll get back to you.

Best regards,
Trowa

TrowaD · Answer

Hi Luigi,

I know your colleague is now working on a solution, I still wanted to post what I have.

Hopefully he will have a way to deal with the inconsistencies in your data. For my code to work the following needs to be cleaned up:
Sheet Deliveroo Data, column A, Brand and Kitchen need to be separated with "-".
Brand and Kitchen data need to match up with sheet Order Details, columns V and U.

Once the clean up is done, the following code will do as requested:
Option Compare Text
Sub RunMe()
Dim SF1, SF2, SF3, mString, lOrder As String
Dim mFind, mFind2, mFind3 As Range
Dim pTime, oTime, tSub, oPT As String

Sheets("Deliveroo Data").Select

For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    SF1 = cell.Value
    mString = cell.Offset(0, -1).Value
    SF2 = Trim(Left(mString, InStr(mString, "-") - 1))
    SF3 = Trim(Mid(mString, InStr(mString, "-") + 1))
    
    With Sheets("Order Details")
    
    Set mFind = .Columns("B:B").Find(what:=SF1, lookat:=xlWhole)
    If mFind Is Nothing Then GoTo NextCell
    firstaddress = mFind.Address
    
    Do
        If .Cells(mFind.Row, "V").Value = SF2 And .Cells(mFind.Row, "U").Value = SF3 Then
            lOrder = Sheets("Order Details").Cells(mFind.Row, "A").Value
        End If
        Set mFind = .Columns("B:B").FindNext(mFind)
    Loop While mFind.Address <> firstaddress
    
    End With
    
    If lOrder = vbNullString Then GoTo NextCell
    
    Set mFind2 = Sheets("Delivery Data").Columns("A:A").Find(what:=lOrder, lookat:=xlWhole)
    Set mFind3 = Sheets("Data").Columns("A:A").Find(what:=lOrder, lookat:=xlWhole)
    
    oTime = Sheets("Delivery Data").Cells(mFind2.Row, "C").Value
    tSub = Sheets("Deliveroo Data").Cells(cell.Row, "E").Value
    oPT = Sheets("Delivery Data").Cells(mFind2.Row, "L").Value * 100
    
    pTime = Format(TimeSerial(0, 0, DateDiff("s", tSub, oTime) + oPT), "hh:mm:ss")
    
    Sheets("Data").Cells(mFind3.Row, "L").Value = pTime
    
NextCell:
Next cell
End Sub

The code will error out, since sheet Deliveroo Data, cell A20 doesn't contain the "-" symbol. For the previous 18 cells, 6 matches were found (due to non matching Brand and Kitchen names), which can be found on sheet Data, column L.

Thanks for the query, it has been a good exercise for me!

Best regards,
Trowa

Punching Time

11 responses

Similar discussions

Subscribe To Our Newsletter!