Forum Discussion

PacificGull08's avatar
PacificGull08
Copper Contributor
Feb 01, 2026

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 

excel
Formulas and Functions

2 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 02, 2026

    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

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 02, 2026

    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

Resources