Matching Times

[Moti]

Hi
I have a column of times that employees clocked in (Column D).
In I2:I13 I have a list of times that the shift actually started.
I am trying to match the time that they actually clocked in to the time that they should have clocked in.
for example, If my employee clocked in at 11:47 - I want to return the value 11:30 showing that he was 17 minutes late
Likewise if he he clocked in at 11:25, I still want to return 11:30 showing that he was 5 minutes early.

My list includes hundreds of clocked times, and I have about 9 shifts per day that I am trying to match.

Using a Mac, I am trying to use an INDEX MATCH Function with the following formula:
INDEX($I$2:$I$13;MATCH(MIN(ABS($I$2:$I$13-D23));ABS($I$2:$I$13-D23);0))
But its returning a #Value Error and I dont know why (Its not the time formatting as far as I can tell)

What am I doing wrong?

 

[PeteWright]

Hi, without knowing your actual data it's hard to tell why this error is popping up.
Could you post the data you're referencing in the formula?

 

[PeteWright]

I get no error.

Which version of Excel do you use? Mac, but what MSO?
Please update your profile so it shows the correct information.

Maybe the error is with the part

ABS($I$2:$I$13-D23)

which actually returns an Array

If you're on Office 2019 or earlier you probably need to enter curly braces and press CTRL+SHIFT+ENTER to make your array formula work:

{=INDEX($I$2:$I$13;MATCH(MIN(ABS($I$2:$I$13-D23));ABS($I$2:$I$13-D23);0))}

Book1
	D	E	F	G	H	I
2						6:00
3						6:30
4						7:00
5						7:30
6						8:00
7						8:30
8						9:00
9						9:30
10						10:00
11						10:30
12						11:00
13						11:30
14
15	11:25	11:30:00
16	11:47	11:30:00
17
18
19
20
21
22
23	11:25	11:30:00
Sheet1

Cell Formulas
Range	Formula
E15:E16,E23	E15	=INDEX($I$2:$I$13,MATCH(MIN(ABS($I$2:$I$13-D15)),ABS($I$2:$I$13-D15),0))

 

[Moti]

Hi PeteWright
Thank you for your answer.

After lots of trial and error, I discovered that it was a time formatting issue all along.
The report gives me times, but they are not formatted as times, and I cant format them correctly myself.

When I type over them, my formula works:

In the screenshot, Column E are the shift times.
Column B are the actual clocked times (The 2 Yellow blocks are the times I entered manually and are therefore formatted correctly, making the formula work as it should).

Do you have a way that I can format the report times correctly without having to manually write over them?
I have tried formatting them to h:mm:ss AM/PM
But excel doesnt change them. Perhaps it doesnt recognize the origional format?

Thanks again

 

[PeteWright]

Do you have a way that I can format the report times correctly without having to manually write over them?
I have tried formatting them to h:mm:ss AM/PM
But excel doesnt change them. Perhaps it doesnt recognize the origional format?

Excel seems to memorize the last set up number format. So if you paste some data Excel tries recognize any of its default formats.
Once done, new pasted data will keep this format no matter what you set up.

Maybe you can try to "import" new data rather then just pasting it using the "Text Import Wizard":

To do this simply copy your data to clipboard and then import it.

Hope it works for you.

 

[Moti]

Thank

Excel seems to memorize the last set up number format. So if you paste some data Excel tries recognize any of its default formats.
Once done, new pasted data will keep this format no matter what you set up.

Maybe you can try to "import" new data rather then just pasting it using the "Text Import Wizard":

View attachment 119343

To do this simply copy your data to clipboard and then import it.

Hope it works for you.

Thank you