Punching Time

Solved/Closed
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020 - Updated on Dec 31, 2019 at 12:47 PM
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020 - Jan 24, 2020 at 03:45 PM
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

11 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 2, 2020 at 11:28 AM
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
1
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Updated on Jan 2, 2020 at 06:21 PM
The problem is that I can't use VLookup unless they have the same Brand name and the name of the branch (I think that's how it works) but in this case, these data are coming from different sources and the name is not matching up so I can't get vlookup working for this or maybe if there's a way to match the "restaurant name" from deliveroo data and the "brand" in data tab.

Here is the file: http://www.ge.tt/2aUQwT03

(To be clear on what i'm trying to do, I need to count the punching time. "Time Submitted" in deliveroo data tab is the time that the portal sent us the order, "Order time" from delivery data tab is the time when the agent is done punching the order and "Order Processing Time" from the same tab is the exact minutes it took the agent punching it or how much time is the system open while the agent is punching the order)
So Order Time - Time Submitted + Order Processing Time is what im trying to do but im on a roadblock here.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 20, 2020 at 12:14 PM
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

1
FacebookCoder
Jan 20, 2020 at 06:29 PM
Oh, it is true! "No good Deed goes unpunished!"
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 21, 2020 at 05:09 PM
Hi TrowaD!

how's your day going? Don't worry about it, it's now being done by my colleague who has knowledge of using jupyter notebook which I have no idea on how to use it haha.

Anyways, thank you so much for always trying to help. You've been great and a life saver.

Talk to you soon!!

Regards,
Luigi
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 23, 2020 at 12:11 PM
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
1
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 24, 2020 at 03:45 PM
Thannk you so much Sir TrowaD! I'll be posting more queries next time. Thank you very much and I mean it from the bottom of my heart!

Regards,
Luigi
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 1, 2020 at 09:15 PM
Please help me with this anyone?
0

Didn't find the answer you are looking for?

Ask a question
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 9, 2020 at 12:25 PM
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
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 9, 2020 at 01:13 PM
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 13, 2020 at 11:47 AM
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
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 14, 2020 at 06:39 PM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 16, 2020 at 12:16 PM
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
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 17, 2020 at 09:20 AM
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.
0
Facebookcoder
Jan 17, 2020 at 10:19 AM
What column in the data sheet?
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Jan 17, 2020 at 04:54 PM
we should add a new column on the last part
0