Monthly schedule - conditional formatting date range that exists in one cell

[DushiPunda]

Greetings,
I have a monthly schedule spreadsheet that I'd like to add some conditional formatting to based on a date range that would exist in one cell.

Example:
B42 = 8/18/24-8/22/24
Conditional formatting applied to C3:AG4

Expected result: Highlight the given dates on my sheet (in this case it would highlight T3:X4).

If necessary, I'm open to having a couple cells outside of the print area if I need to use a formula to split the range into two different cells.

I was able to do this split by doing:

=LEFT(B42,FIND("-",B42&"-")-1)

=RIGHT(B42,FIND("-",B42&"-")-1)

Now just need to concoct a conditional formatting formula that looks at those dates and highlights the necessary cells.

 

[DushiPunda]

Just played around a little with this.
1.

=RIGHT(B42,FIND("-",B42&"-")-1)

changed to

=RIGHT(B42,FIND("-",B42&"-")+1)

Note for later: the LEFT formula is in cell B45 and the RIGHT formula is in B46
2. I tried the following conditional formatting formulas:

=AND(DATEVALUE(C$3)>=DATEVALUE($B$45),DATEVALUE(C$3)<=DATEVALUE($B$46))

=AND(C$3>=$B$45,C$3<=$B$46)

Neither of these worked.

I decided to check if excel was reading my dates as actual dates, but I encountered the following:

=DATEVALUE(B45)(

Return: 45522

=DATEVALUE(B46)

Return: 45526

=DATEVALUE(T3)

Return: #VALUE!

T3 contents: "8/18/2024" formatted with a custom to only show the day (d). So it appears that excel isn't reading T3 as a date?



Edited to add:
This doesn't appear to be affecting any of my other conditional formatting based on dates. For example, I have a holidays section (B39 & B40). For example, Independence Day is in cell B39 on my July sheet. My conditional formatting is:

=OR(C$3=$B$39,C$3=$B$40)

Applied to the same range mentioned in the first post:
C3:AG4

 

[myall_blues]

Try this:
Select the range C3:AG4
Conditional formatting->New Rule->Use a formula->In the formula bar add this formula:
=AND(C4>=DATEVALUE(LEFT($B$42,FIND("-",$B$42)-1)),C4<=DATEVALUE(MID($B$42,FIND("-",$B$42)+1,99))). Set the formatting how you want and then OK out.

 

[DushiPunda]

That worked...thanks @myall_blues!

 

[myall_blues]

You’re welcome. Thanks for responding back.