Conditional Formatting or probably a Gant Chart

[Heysweden]

Hi, I'm currently making a excel template on which I will manually enter Start Time and End time.

After inputting start time and end time in should color the cells between the start and end time.

I need it in a 24 hour format, were when I tried if start time is less than end time, then it is working perfectly, but if end time is greater than start time it is not highlighting the cells.

For e.g My start time is 15:00 at the evening and end time is at 2 at early morning next day, then it should highlight the cells from 15 of the current day to the next day till 2.

I would appreciate your help on how to fix this.

 

[awoohaw]

The values in column A and B should be dates, but format only the time component, Row 3 should also be treated the same way.
(the formulas in the cells are what I used to confirm the conditional formatting rule, they do not need to be in your worksheet).

Book1
	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	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1
2	Start	End	Hours	Date	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	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
3	00:00	23:00	23:00		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4	15:00	02:00	11:00		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5	19:00	23:00	04:00		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6	03:00	04:00	01:00		FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
7	08:00	11:00	03:00		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
E2:AZ2	E2	=DATE(2023,11,10) + SEQUENCE(1,48,0,1)/24
E3:AZ7	E3	=AND($A3<=E$2,$B3>E$2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:AZ7	Expression	=AND($A3<=E$2,$B3>E$2)	text	NO

 

[Heysweden]

Hi, many thanks for your support, you were like an angel to me, because I was stuck and unsure on how I would solve this since I also a newbie in excel. Coming to the point I tried your method and it worked perfectly fine, but I just want to check with you by giving an example. I have a vehicle that is coming to a parking location, so when the vehicle arrives that will be my start time and when the vehicle leaves it will be end time. I'm creating a schedule for a week were I will mark the days and time when the vehicle is at the parking space. For e,g if the vehicle reaches on 12th Nov from morning 8 to evening 16, I will highlight the cells on that duration, but on the same row I should highlight the cells when the vehicle coming back on the next day ie 13th Nov may be at evening 15 till midnight 22(were the duration of both the days should highlight), so it will be like one vehicle on different days with different duration on a same row. Sorry for the long explanation, as an input I can have both date and time. Thanks in advance.

 

[awoohaw]

Okay, so to summarize your question: You're asking for what if two different time groups are used for the same row?

I'm thinking you need to record a second seven sets of time start/duration. Or you'll have to repeat the same row for that parking location.

Or you can have another way of looking at/processing your scenario. One section of your worksheet/workbook to record the
Space Number, Start Time, Duration. and then use that to fill in your gannt chart with different calculations.

 

[Heysweden]

Okay, so to summarize your question: You're asking for what if two different time groups are used for the same row?

I'm thinking you need to record a second seven sets of time start/duration. Or you'll have to repeat the same row for that parking location.

Or you can have another way of looking at/processing your scenario. One section of your worksheet/workbook to record the
Space Number, Start Time, Duration. and then use that to fill in your gannt chart with different calculations.

Yes, I have two different time groups for the same row, that means if I select a date and time, then it should highlight cells for that particular date and duration, on the same row if I change to another date and time, then both should highlight.