Forum Discussion
Change format at a particular date and time
Hi
I have a spreadsheet that I want to highlight cells containing today and before at a particular time of day (7PM ) not midnight. Is this possible?
Thanks
David
Hello PacificGull08,
Yes, this is possible using Conditional Formatting.
Use this formula (assuming the date/time is in A1):
=A1 <= TODAY() + TIME(19,0,0)Steps:
- Select the range
- Go to Home > Conditional Formatting > New Rule
- Choose "Use a formula to determine which cells to format"
- Enter the formula
- Choose a format and click OK
This highlights any date/time that is today or earlier than 7:00 PM, instead of midnight.
Use conditional formatting to highlight information in Excel
4 Replies
Hello PacificGull08,
Yes, this is possible using Conditional Formatting.
Use this formula (assuming the date/time is in A1):
=A1 <= TODAY() + TIME(19,0,0)Steps:
- Select the range
- Go to Home > Conditional Formatting > New Rule
- Choose "Use a formula to determine which cells to format"
- Enter the formula
- Choose a format and click OK
This highlights any date/time that is today or earlier than 7:00 PM, instead of midnight.
Use conditional formatting to highlight information in Excelmaybe I misunderstood but I assumed they needed items that are today AND <7pm
another option for the formula could be:
=(A1>=TODAY()) * (A1<TODAY+TIME(19,0,0))
If I understand correctly, the answer is yes. Select the range you want to check/highlight and then you need to use Conditional Formatting and choose 'Use a formula to determine which cells to format'. Then enter the following formula:
=(INT(A1)=TODAY())*(MOD(A1,1)<TIMEVALUE("7:00 pm"))and today being 2/2/2026 you can see only the times < 7pm are highlighted:
in the formula the "A1" references must be the upper left most cell of the range you select (i.e. the 'applied to' range)
the (INT(A1)=TODAY()) will check if the date is today's date,
and the (MOD(A1,1)<TIMEVALUE("7:00 pm")) will check if the time portion of that date-time is less than 7pm
Hi
To clarify things could you post a picture showing a few rows/column where your date/datetime values are & manually highlight those you expect to auto-highlight? Thanks
