+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting, letter, colour, number and add, in staff annual leave tracker

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Smile Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Hello,
    I'm new to this forum and a newbie at Excel and I'm hoping I'm posting this message in the right place on this forum.
    I'm in the process of making an annual staff leave tracker in hours, in Excel. At this point, now after several hours of searching and trying I have carefully and step by step put together a range that looks like a table, (but NOT formatted as such e.g. via "Ctrl+T"), that has horizontally across the top the dates of the whole year (2020) and vertically on the left side a list of the employees ( 20 in total). In the field between these axes, I would like to input a letter and a number, where the letter would change the colour of the cell and the number would refer to the number in hours, to be added later, or to rephrase:

    1. I click on the required cell in the range ( coordinates: employee name, date)
    2. I enter a letter: "a" for annual leave, "s" for sick leave and "i" for any other leave. Ideally, at this point, I would like the colour of the selected cell to change in line with the colour I have assigned through conditional formatting ( or any other way, suggestions welcome), to the letter ( "a", "s", or"i") being used.
    3. I enter a number (in the same cell), to display the number of hours involved. This number will be used to calculate to the total number of hours leave taken or due for that employee.

    I'm beginning to wonder now if this is at all possible?

    Apologies for the long story, but I would be grateful if you could help in any way.

    Looking forward to hearing from you.

    Regards

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    a sample would be useful
    but to sum the different letters
    you can use an array formula
    Use control + Shift +enter for {} around the formula - although Excel 365 now does accepts arrays automatically

    =SUM(IF(LEFT(B2:Z2,1)="S",VALUE(MID(C2:Z2,2,5))))

    That assumes entry like S 2.5
    and the Z change to the last column in your year list

    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:
    See the yellow banner at top of screen
    UserName Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Last edited by etaf; 07-30-2020 at 06:50 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    i added a post before, whuch i could not edit - now disappeared

    you can use conditional formatting to change the colour based on the first character in the cell

    for 2007, 2010 , 2013 or 2016 excel version
    Conditional Formatting

    Highlight applicable range >>

    B2: whatever letter the year stops row 21



    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:


    =Left(B2,1)="S"


    FormatÖ [Number, Font, Border, Fill] - format for the colour you want for S
    choose the format you would like to apply when the condition is true
    OK >> OK

    Now add additional rules for the other conditions , A and I

    I assume you will leave a space before the number

    In order to add up the numbers , will be a little different as you cannot just do a SUM() as its text because you have a letter at the start

    is that how the data will be setup - OR will you have different cells for the value

    You may need VBA (not my expertise) to sum based on colour of fill

  4. #4
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Hello Wayne,
    Thank you for your answer and indeed, what I'm aiming for is to ideally have only the number in the cell, whilst the letter is input only to change the cell's background colour. The number will be used to be added up to calculate the sum cross the row for that particular colour (read "leave" coded as "s", "i" or "a"). So I don't need the letter to show in the cell, as long as the colour is displayed.
    I'm attaching the file to this post, hope it goes well.
    Thanks again for your time, speak to you soon,
    Duran

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    don't see a file
    You will need a letter in the cell, otherwise excel will not know what colour to assign
    you are just summing all values then =SUM(VALUE(MID(B2:Z2,2,5))) would work
    regardless of the code
    and example would be good on how layout

  6. #6
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Hello Wayne,
    Thank you for your reply
    I'm trying to upload the file again.
    How can I check that the file has been uploaded and attached, before I send off the email?
    Speak to you soon,
    Duran
    Attached Files Attached Files

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    i have applied the 3 conditional format rules as I suggested above
    and also the sumif based on s i & a as suggested above
    Attached Files Attached Files
    Last edited by etaf; 08-01-2020 at 05:37 PM.

  8. #8
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Red face Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Hello Wayne,
    Thank you, I'm so grateful for that!

    Just so that I can understand what you have done, can you confirm
    1. You highlighted the input cells of the field 1/1/2020 to 31/12/2020, and applied conditional formatting to the letters "s", "i" and "a"
    2. You wrote an array formula with an "IF" statement in the 1st cell of the respected columns of IL, SL and AL and copied these down each column by dragging down

    I still have a lot to learn about formulas, but I would be grateful if you could help me to understand the numbers"1", "2" and "5" as they appear in the formula. How do you get to these?

    I like to thank you again for putting in the time and effort to help me out with this. Thank You!:

    Duran
    Last edited by dsk05a; 08-02-2020 at 06:47 AM. Reason: typo

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    yes, pretty much
    =SUM(IF(LEFT(B2:Z2,1)="S",VALUE(MID(C2:Z2,2,5))))

    The 1 is using the Left() function
    It will look in the cell specified . and then take from the left the number of characters
    in this case just 1
    But if wanted the 3 most left characters then Left(Cell, 3)
    There is also a Right() and a MID()

    THE MID()
    is the cell you want to extract text from
    then 2 is how many characters from the left you want to start
    and the last number is how many to extract, 5 in my case , as I thought the most you would enter is say around 23 and 3/4 hrs in any day

    so 23.75 which is 5 characters

    so if you had ABCDEF
    Left( cell, 2) = AB
    Right ( cell, 3) = DEF
    Mid ( cell, 2,3) = BCD

    hope that helps
    Last edited by etaf; 08-02-2020 at 07:13 AM.

  10. #10
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Hi Wayne,
    Thanks again!
    Have a great day!
    Duran

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    you are welcome

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Registered User
    Join Date
    07-29-2020
    Location
    Newport, Wales
    MS-Off Ver
    365
    Posts
    7

    Re: Conditional formatting, letter, colour, number and add, in staff annual leave tracker

    Apologies,
    but I can't locate "Thread Tools" on this page.
    Would you mind showing me where I can find it?
    Thanks

+ 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. Formula Help for Annual Leave Tracker
    By Roquet_Man in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-11-2017, 01:47 PM
  2. [SOLVED] Conditional Formatting on this Annual Leave Form
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2016, 01:23 PM
  3. Formulas for staff leave tracker and perpetual calendar
    By philsmith101 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2016, 01:58 AM
  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. NETWORKDAYS (for annual leave tracker)
    By februarylondon in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-08-2009, 05:39 AM
  7. annual leave planner for approx 100 staff members
    By wally in forum Excel General
    Replies: 1
    Last Post: 03-22-2005, 08:06 AM

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