+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting with time range

  1. #1
    Registered User
    Join Date
    11-29-2017
    Location
    South Dakota USA
    MS-Off Ver
    2003 Excel
    Posts
    4

    Conditional formatting with time range

    Hi! I am working with a daycare and have an excel sheet with attendance. I am trying to set up some sort of conditional formatting (not picky on the result) which indicates whether the child was present for a certain timeframe (meal time). I have been working with conditional formatting, and also with the logical IF statement, but cannot get the cell to either highlight or mark when the statement is true but NOT if false. This is what I am trying to accomplish:

    If InTime < 8:00 AM and OutTime > 7:00 AM, then mark (highlight, X, whatever) the "Breakfast" column.
    In other words, if they were there for at least 1 minute of the meal time (7-8AM), they are counted as being there for that meal. Can anyone help me?

    Thanks,
    Heather

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,695

    Re: Conditional formatting with time range

    Hello Heather,

    In Excel with times and dates 1 = 1 day, so you can represent 7 AM with 7/24, so for a conditional formatting formula try something like this:

    =AND(A2<8/24,B2>7/24)

    Where A2 contains start time and B2 end time
    Audere est facere

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Conditional formatting with time range

    Try this in the Breakfast column....change the cells to the ones you need
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional formatting with time range

    Hi

    With in time in A1 and out time B1 then a formula to mark a cell is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The equivalent conditional format is of course just
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which returns True or False
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    11-29-2017
    Location
    South Dakota USA
    MS-Off Ver
    2003 Excel
    Posts
    4

    Re: Conditional formatting with time range

    Thanks so much for the fast replies!! I was able to get my formula to work, and even got the other meals set up based on breakfast. The only problem is I have found at least two examples where the kid left DURING the meal and the formula does not come back with TRUE. The formula is =IF(AND(D2<18/24,F2>19/24),"X","")
    D2=6:54 AM
    F2=6:34 PM
    Since she was there for 34 minutes of dinner there should be an X but it is blank. What am I doing wrong?

  6. #6
    Registered User
    Join Date
    11-29-2017
    Location
    South Dakota USA
    MS-Off Ver
    2003 Excel
    Posts
    4

    Re: Conditional formatting with time range

    I understand that. This is for dinner which is from 6-7PM.

  7. #7
    Registered User
    Join Date
    11-29-2017
    Location
    South Dakota USA
    MS-Off Ver
    2003 Excel
    Posts
    4

    Re: Conditional formatting with time range

    Never mind I figured out what I was doing wrong. I was swapping the times so the earlier one was first. It is working great now, thanks everyone!

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Conditional formatting with time range

    Glad you were able to get it figured out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula needed for conditional formatting between time range over 2 columns
    By index9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-20-2016, 08:26 AM
  2. Conditional Formatting time with a range applied
    By andyp24 in forum Excel General
    Replies: 7
    Last Post: 07-20-2016, 03:51 AM
  3. Conditional formatting / VBA, based on selected time range
    By russkris in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2013, 07:45 PM
  4. Replies: 5
    Last Post: 11-27-2013, 06:04 PM
  5. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  6. Conditional Formatting - Grey Out and Lock A range based on a cell value in that range
    By Excelgnome in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2012, 07:31 PM
  7. Replies: 4
    Last Post: 01-11-2012, 07:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1