Hi Kaspars,

Welcome to our forum! Please try uploading your file again. You need to click the 'Start Upload' button after selecting your file.

Thanks,

Mynda

Sorry, the initial file was exceeding the limit.

Hi Kaspars,

Do you have Microsoft 365/Office 365? I need to know what version of Excel you have so I know what functions you have available.

Also, do you want these results for each date in the dataset?

Mynda

Yes, it would be 365.  Yes- please it would be for each date. If it makes things easier the time could be formatted in the numeric value.

Thank you 🙂

Hi Kaspars,

Thanks for clarifying. Your first example, "Minutes past target confirmation time of 14:10" has 2:38 PM as the earliest confirmation time after 2:10 PM, however on row 6631, 6632 and 6633 there is a time of 2:34:39 PM. 

Did you simply overlook this earlier time, or is there something else I need to take into account, as the data isn't sorted.

Mynda

Sorry Mynda, my mistake. Data wasn`t sorted correctly. I have changed it now. It should be sorted as basic as the earliest data and earliest time.

Hi Kaspars,

Thanks for clarifying. Please see file attached which uses the new dynamic array functions: 

XLOOKUP Function

FILTER Function

Kind regards,

Mynda

Thank you Mynda, that is excellent!!! And it is another reminder that I need to learn a lot about Excel. 

If it is possible, could you please adjust first time slot as - 1st Gap after 14:00, instead of 14:10. If it is time-consuming please leave it 🙂 I tried to do it today, but no luck- only more questions in my mind 😀

Also, I have attempted (again unsuccessfully) to add more data to the sheet which consists of your formulas, as I have another 104 users to add and calculate. Thought that copying/pasting additional data and dragging down the formula would work- but it didn't 🙁 Which is again my lack of knowledge so far. 

Hi Kaspars,

You can change the formula in cell H3 to this:

=XLOOKUP(G3#+0.583333333333333,Table1[Date Time],Table1[Confirmation Time],"On Time",1)-0.583333333333333

0.583333333333333 is the time serial number for 14:00. If you enter 14:00 in a cell and then format it as general, you'll get 0.583333333333333.

More on working with dates and time in Excel here.

The source data in columns A:E is formatted in an Excel Table. All you need to do is paste your new data on the very next blank row at the bottom of the table and the ranges will expand to incorporate the new data in the Table's range. This will in turn automatically feed through to the formulas. As more dates are added, you'll need to copy the formulas in columns I:K down.

Note: the formulas don't differentiate between different users' data. If you want separate results for the different users then let me know and provide sample data containing multiple users.

Mynda

Thank you, another set of new information for me to learn 🙂 I have attempted numerous times to adjust the formula, but no success. I believe my MO Excel doesn't support XLOOKUP 🙁 I might be wrong, but I have that assumption. I will add screenshots.

*Understand now the point about pasting new data and adjusting formula range.

*If we add more data with multiple users, would I be able to select a filter on - Confirmed by and then see the calculation?

Preferably I would calculate these measures every 3 months, so trying to create it dynamically, so I can add the data, do the calculation and after 3 months remove existing data and update with a new one.

Hi Kaspars,

You need to change your update channel from semi-annual to monthly to get the XLOOKUP function. Speak to your IT people to make that happen.

If you want to filter the data by the user, you'd need to modify the formulas to include a filter for the selected user ID. See attached, where you can choose the user from the drop down list in cell G2. There's only one user in the list at the moment, but when you update the data, it will automatically include new user IDs.

Mynda

Thank you, Mynda, much appreciated 🙂

HI Mynda, sorry to ask again, but I still can`t get the results. I must be doing something wrong..  I will add the recent file which you have created and new data for the user2. 

Steps that I am taking:

1. Copying columns A & B from file  user2 data

2. Pasting them into columns A & B within file kaspars_time_calcs2

3. It updates the formulas successfully in Columns C & D

4. Copying column C from file  user2 data

5. Pasting them into column C within file kaspars_time_calcs2

6. This adds another user in the filter G2

So far so good 🙂

The problem is that it doesn't update the data into G: K Columns

What am I missing, please?

Many Thanks

Kaspars

Hi Kaspars,

It works fine for me. Did you get your version of Excel updated to include the dynamic array functions?

Mynda