'Match' function not working as planned matching time

[juanbolas]

Hi everyone,

I'm trying to get the following sheet to work but got stumped. The bottom block trees to match its time column to the times set in the block above. But, for some reason, for times greater than or equal to 13:00 (military time) it shows #N/A. It looks like it has something to do with the processing of military times but I'm not sure how to get around it.

Thanks in advance for your help.

Juan

Tasklist v2 for question v0.1.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		Day starts				9:00
4
5				Tasks	Length	Start	End
6		Top 3	1	Thnk & plan	00:30	09:00	09:30
7			2	Task 2	01:00	09:30	10:30
8			3	Task 3	00:30	10:30	11:00
9		Top 5	4	Task 4	00:30	11:00	11:30
10			5	Task 5	02:00	11:30	13:30
11		Unplanned	6	Task 6	00:30	13:30	14:00		Things seem to start to go wrong when the hour is => 13:00
12			7	Task 7	00:30	14:00	14:30
13			8	Task 8	00:30	14:30	15:00
14			9	Task 9	01:30	15:00	16:30
15			10	Task 10	00:30	16:30	17:00
16								Match
17		Slot	Time	Task	Interruptions	Length	Task #	only
18		1	9:00	Thnk & plan			1	1
19		2	9:30	Task 2			2	2
20		3	10:00	Task 2			2	#N/A	This #N/A is correct
21		4	10:30	Task 3			3	3
22		5	11:00	Task 4			4	4
23		6	11:30	Task 5			5	5
24		7	12:00	Task 5			5	#N/A	This #N/A is correct
25		8	12:30	Task 5			5	#N/A	This #N/A is correct
26		9	13:00	Task 5			5	#N/A	This #N/A is correct
27		10	13:30	Task 6			6	6
28		11	14:00	Task 6			6	#N/A	This should match to task 7 (line 7 above)
29		12	14:30	Task 6			6	#N/A	This should match to task 8 (line 8 above)
30		13	15:00	Task 6			6	#N/A	This should match to task 9 (line 8 above)
31		14	15:30	Task 6			6	#N/A
32		15	16:00	Task 6			6	#N/A
33		16	16:30	Task 6			6	#N/A	This should match to task 10 (line 8 above)
34		17	17:00	Task 6			6	#N/A
35		18	17:30	Task 6			6	#N/A
36		19	18:00	Task 6			6	#N/A
37
Dayplan

Cell Formulas
Range	Formula
C6:C15	C6	=ROW()-ROW($C$6)+1
F6	F6	=F3
G6:G15	G6	=F6+E6
F7:F15	F7	=IF(C7=1,F$3,F6+E6)
D7:D15	D7	="Task "&TEXT(C7,"0")
B18:B36	B18	=ROW()-ROW($B$18)+1
C18:C36	C18	=DayStartTime+(B18-1)*"0:30"
D18:D36	D18	=OFFSET($D$5,$G18,0)
G18:G36	G18	=IF(ISNA(MATCH(C18,F$5:F$16,FALSE)),G17,MATCH(C18,F$5:F$16,FALSE)-1)
H18:H36	H18	=MATCH(C18,F$5:F$16,FALSE)-1

 

[Toadstool]

Hi Juanbolas,

Your challenge is due to the way Excel handles precision. While two numbers may look the same on the screen there may be differences in actual storing of that number.

Excel stores dates as an integer and time as a fraction. One second is stored as a value of 0.0000115740740740741 so you can use the MROUND function to do your calculations to the nearest second.

In F6 and copied down to F15 use:

=IF(C6=1,MROUND(F$3,0.0000115740740740741),MROUND(F5+E5,0.0000115740740740741))

In C18 and copied down to C36 use:

=MROUND(DayStartTime+(B18-1)*"0:30",0.0000115740740740741)

 

[juanbolas]

Thanks! Works great! I tried doing a normal 'round' before but it didn't work. I'll have to investigate the differences between 'round' and 'mround'.

Again, thanks toadstool!

Hi Juanbolas,

Your challenge is due to the way Excel handles precision. While two numbers may look the same on the screen there may be differences in actual storing of that number.

Excel stores dates as an integer and time as a fraction. One second is stored as a value of 0.0000115740740740741 so you can use the MROUND function to do your calculations to the nearest second.

In F6 and copied down to F15 use:

=IF(C6=1,MROUND(F$3,0.0000115740740740741),MROUND(F5+E5,0.0000115740740740741))

In C18 and copied down to C36 use:

=MROUND(DayStartTime+(B18-1)*"0:30",0.0000115740740740741)

 

[juanbolas]

Thanks! Works great! I tried doing a normal 'round' before but it didn't work. I'll have to investigate the differences between 'round' and 'mround'.

Again, thanks toadstool!

 

[Toadstool]

You're welcome. Thanks for the feedback.