Half Day Conditional formatting

[oO P2K Oo]

Hi All,

I am trying to make a Gantt chart with a twist! I wish to split each day into AM and PM sessions. However I am looking for a formula that will work with conditional formatting, such that if the time is between 00:00-11:59 the cell is highlighted, and when the time is between 12:00-23:59 the corresponding cell is highlighted and the previous one is un-highlighted. I would be happy if the changes were updated when the workbook is saved, as I am sure the now formula should be used, and I understand it won't show until refreshed/saved.
I have added the "date and time" as an excel serial (so 44892 represents 27/11/22 AM and 44892.5 is 27/11/22 PM). So I am trying to link my NOW() or TODAY() formula to this value, but it just keeps churning out rubbish.
Non-macros preferred please, but open to VBA.

Any and all help would be appreciated.

 

[PeteWright]

Hello.

Unfortunately I have absolutely no idea how your data looks like and what you mean.
Could you be more specific od provide some samples? At least some dummy data.

The below table is what I thought you need, but it most likely isn't.

Book1.xlsm
	A
1	Date & Time
2	11/26/2022 18:47
3	11/26/2022 20:15
4	11/26/2022 23:59
5	11/27/2022 0:00
6	11/27/2022 4:04
7	11/27/2022 7:01
8	11/27/2022 11:59
Sheet14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	= AND(A2 - INT(A2) > 0.5, A2 - INT(A2) <= 1)	text	NO
A2:A8	Expression	= AND(A2 - INT(A2) >= 0, A2 - INT(A2) < 0.5)	text	NO

 

[oO P2K Oo]

I have tried to install the mini-sheet, but it won't let me install properly. I can select the range but copying it and showing the list of conditional formats are not proving successful at all.

so at best, I can provide you with screenshots.

So in cell BM15, I would like this to be shaded when the time is between 27/11/2022 00:00 and 27/11/2022 11:59. When the time changes to 27/11/2022, the cell BM15 is unshaded and the cell BN15 is shaded instead to represent that this cell is where the current time falls, so when viewing the overall project you have a visual indication of where you are in relation to the project.

Thank you for viewing my post and offering to help

 

[PeteWright]

I adapted your data, played around a little bit and I think, I've figured it out...

Here we go:

Book1.xlsm
	A	B	C							J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X																													BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
1
2
3
4
5
6		Project Start Date	10/31/2022
7
8
9										Week	Week 1																																										Week 4														Week 5
10										Week starts on	31/10/2022																																										21/11/2022														28/11/2022
11										day of month	31		1		2		3		4		5		6																														21		22		23		24		25		26		27		28		29		30		1		2		3		4
12										day of week	M		T		W		T		F		S		S																														M		T		W		T		F		S		S		M		T		W		T		F		S		S
13										AM/PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM																													AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM	AM	PM
14
15										NOW
16
Sheet12

Cell Formulas
Range	Formula
BA9,BO9	BA9	= AM9+1
K10	K10	= C6
BA10,BO10	BA10	= AM10+7
K11	K11	= K10
M11,O11,Q11,S11,U11,W11,BA11,BC11,BE11,BG11,BI11,BK11,BM11,BO11,BQ11,BS11,BU11,BW11,BY11,CA11	M11	= K11+1
K12,M12,O12,Q12,S12,U12,W12,BA12,BC12,BE12,BG12,BI12,BK12,BM12,BO12,BQ12,BS12,BU12,BW12,BY12,CA12	K12	= CHOOSE(WEEKDAY(K11,2),"M","T","W","T","F","S","S")
K13,M13,O13,Q13,S13,U13,W13,BA13,BC13,BE13,BG13,BI13,BK13,BM13,BO13,BQ13,BS13,BU13,BW13,BY13,CA13	K13	=K11
L13,N13,P13,R13,T13,V13,X13,BB13,BD13,BF13,BH13,BJ13,BL13,BN13,BP13,BR13,BT13,BV13,BX13,BZ13,CB13	L13	=K11+0.5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K15:CB15	Expression	= NOT(MROUND(K$13,0.5) = MROUND( NOW(), 0.5))	text	NO
K15:CB15	Expression	= MROUND(K$13,0.5) = MROUND( NOW(), 0.5)	text	NO

Here's how it looks on my screen with all conditional, number and cell formats: