+ Reply to Thread
Results 1 to 4 of 4

Highlight cells if staff has exceeded leave entitlement

  1. #1
    Registered User
    Join Date
    04-07-2020
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    45

    Highlight cells if staff has exceeded leave entitlement

    Hi,

    I am looking for a way to conditional format a table in which Staff has exceeded his leave entitlement.

    I have attached an example.

    Leave accrued each month on pro rated basis is mentioned in Grey color from C3 to L3
    A staff can take max leave depending on what he she has accrued each month (2.5 each month)

    eg: STAFF 1
    Leave already taken = 12

    Can take max 24.5 days leave until Nov 2020
    However 12 + 5 + 4 + 8 is 28 (which is exceeding the entitlement until that month)

    Any cell above entitlement should be highlighted with Red.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Highlight cells if staff has exceeded leave entitlement

    Conditional formatting rule for C5

    =IF(AND(C5<>"",SUM($C5:C5)>(COLUMN()-2)*2.5),1,0)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-07-2020
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    45

    Re: Highlight cells if staff has exceeded leave entitlement

    Thank you but cant I use the same conditional formatting from C5 to L9 ?

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Highlight cells if staff has exceeded leave entitlement

    Please try CF at C5:L9

    =(SUM($B5:C5)>C$3)*C5

+ 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. Timesheet with Highlight Annual Leave and auto populate hours in cells
    By ismailshajji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2019, 07:25 PM
  2. [SOLVED] Annual Leave entitlement
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2019, 12:44 PM
  3. Tracking Staff Leave
    By itsawayoflife in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2018, 09:15 PM
  4. [SOLVED] % of staff on annual leave
    By tfograham in forum Excel General
    Replies: 6
    Last Post: 07-22-2015, 03:46 PM
  5. Staff Annual Leave Planner Vba
    By simran555 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2015, 05:45 PM
  6. staff leave and sick leave planner
    By Just granite in forum Excel General
    Replies: 1
    Last Post: 05-25-2012, 03:15 AM
  7. Replies: 6
    Last Post: 09-12-2009, 04:14 PM

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