Help with issues calculating using dates

[eraust]

Hi All

I'm attempting to make a schedule which autopopulates/calculates based on a start/finish time.

I've asked for help previously with different versions of this schedule but this particular version, I'm having an issue with the activities that lasts 10 hours.

Basically, if the start time is 04:00, 07:00, 10:00 or 13:00 - it's populating 11 hours rather than 10. This isn't happening with the activities that last 6 or 8 hours or any other start times other than those listed. I've tried checking the format of all the elements that are involved in my formula and everything seems to be the same (custom, hh:mm) so I can't figure out what's going wrong.

I'm sure there are different ways of making this work but I'd rather keep the formulas I'm using the same if possible.

Thanks in advance all

SampleSchedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1				Start time	End time	00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00		00:00	01:00	02:00	03:00	04:00	05:00	06:00
4	Mon	01/05	Activity 1	06:00	16:00							Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1
5	Mon	01/05	Activity 2	07:00	17:00								Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2
6	Mon	01/05	Activity 3	08:00	14:00									Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3
7	Mon	01/05	Activity 4	09:00	17:00										Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4
8	Mon	01/05	Activity 5	10:00	18:00											Activity 5	Activity 5	Activity 5	Activity 5	Activity 5	Activity 5	Activity 5	Activity 5
Schedule

Cell Formulas
Range	Formula
A4:A8	A4	=TEXT($B$4,"ddd")
B4:B8	B4	=$A$2
E4:E8	E4	=IF(D4<>"",_xlfn.XLOOKUP(C4,Sheet2!$A$2:$A$6,Sheet2!$B$2:$B$6)+D4,"")
AE4:AK8,F4:AC8	F4	=IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))

Cells with Data Validation
Cell	Allow	Criteria
D4:D8	List	=Sheet2!$A$10:$A$33

 

[JamesCanale]

I think something is going on in Sheet 2 where you lookup the duration of the activities. I suspect you have a rounding error. Look at this:

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1				Start time	End time	12:00:00 AM	1:00:00 AM	2:00:00 AM	3:00:00 AM	4:00:00 AM	5:00:00 AM	6:00:00 AM	7:00:00 AM	8:00:00 AM	9:00:00 AM	10:00:00 AM	11:00:00 AM	12:00:00 PM	1:00:00 PM	2:00:00 PM	3:00:00 PM	4:00:00 PM	5:00:00 PM	6:00:00 PM
2
3
4	Sat	0	Activity 1
5	Sat	0	Activity 2	7:00	17:00								Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2
6	Sat	0	Activity 3	7:00	17:00								Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3
7	Sat	0	Activity 4
8	Sat	0	Activity 5
9
10
11					0.4166666666667
12
13					0.4166670000000
Sheet20

Cell Formulas
Range	Formula
A4:A8	A4	=TEXT($B$4,"ddd")
B4:B8	B4	=$A$2
F4:X8	F4	=IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
E5	E5	=D5+E11
E6	E6	=D6+E13

 

[eraust]

Ok I see what you mean.

I've checked the sheet and they all seem to be the same. I'm also not sure if that would explain why it's only some start times that are off? If I put the start time down as 06:00 or 21:00 for example, it displays 10 hours.

 

[Anthony47]

When you work with timings you often need to use ROUND; so my proposal: in F4

=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))

 

[eraust]

When you work with timings you often need to use ROUND; so my proposal: in F4

=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))

Is there a way to apply this to my Sheet 2, which is where I'm getting my durations from rather than adding the 10 into the formula?

Right now this sheet has 5 activities on it, but the real version has over 50 with all different durations. So I have a Sheet 2 which is essentially the name of the activity along with the duration in hh:mm. Column E looks up the duration of each activity in Sheet 2 and calculates based on the start time.

 

[Anthony47]

I have been working on Sheet20, the formula in F4 that then has to be copied to the right and down;
not =IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
but the one with ROUND

 

[JamesCanale]

You might cheet, by subtracting a small amount from the end time:

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1				Start time	End time	12:00:00 AM	1:00:00 AM	2:00:00 AM	3:00:00 AM	4:00:00 AM	5:00:00 AM	6:00:00 AM	7:00:00 AM	8:00:00 AM	9:00:00 AM	10:00:00 AM	11:00:00 AM	12:00:00 PM	1:00:00 PM	2:00:00 PM	3:00:00 PM	4:00:00 PM	5:00:00 PM	6:00:00 PM
2
3
4	Sat	0	Activity 1
5	Sat	0	Activity 2	7:00	17:00								Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2
6	Sat	0	Activity 3	7:00	17:00								Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3
7	Sat	0	Activity 4
8	Sat	0	Activity 5
9
10
11					0.4166666666667
12
13					0.4166670000000
Sheet20

Cell Formulas
Range	Formula
A4:A8	A4	=TEXT($B$4,"ddd")
B4:B8	B4	=$A$2
M5:W6	M5	=IF($D5="","",(IF(AND(M$1>=$D5,M$1<$E5-0.0001),$C5,"")))
X5:X6	X5	=IF($D5="","",(IF(AND(X$1>=$D5,X$1<$E5+0.0001),$C5,"")))
E5	E5	=D5+E11
E6	E6	=D6+E13

 

[Anthony47]

You might cheet, by subtracting a small amount from the end time

Round do that job
And theoretically there is a problem even with the Start Time

 

[eraust]

I have been working on Sheet20, the formula in F4 that then has to be copied to the right and down;
not =IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
but the one with ROUND

So I applied this and it works on the activities that last 10 hours. However, my activities that last 6 and 8 are now out of sync with the issue that I was having with the 10 hour ones.

6 hour durations are now displaying as 7 hours and 8 as 9.

 

[Anthony47]

Using your data plus two new activities I get the following, that seems to be correct:

Cartel5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1				Start time	End time	00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00
2
3
4	Mon	01-mag	Activity 1	06:00	16:00							Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1	Activity 1
5	Mon	01-mag	Activity 2	07:00	17:00									Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2	Activity 2
6	Mon	01-mag	Activity 3	08:00	14:00									Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3	Activity 3
7	Mon	01-mag	Activity 4	09:00	17:00										Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4	Activity 4
8	Mon	01-mag	Activity 5	10:00	18:00												Activity 5	Activity 5	Activity 5	Activity 5	Activity 5	Activity 5	Activity 5
9	Mon	01-mag	New 1	10:00	16:00												New 1	New 1	New 1	New 1	New 1
10	Mon	01-mag	New 2	10:00	18:00												New 2	New 2	New 2	New 2	New 2	New 2	New 2
11
Foglio3

Cell Formulas
Range	Formula
F4:X10	F4	=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))

Is it wrong?