+ Reply to Thread
Results 1 to 10 of 10

Formula Help for Annual Leave Tracker

  1. #1
    Registered User
    Join Date
    04-11-2017
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    Formula Help for Annual Leave Tracker

    Hello,

    I've built a rudimentary spreadsheet to help our healthcare team plan and keep track of annual leave. I want a formula where Excel will recognise the word "LEAVE" in the cell and also perform a sum of hours people input next to it i.e.

    Taken C D
    12. "LEAVE 7.5" "LEAVE 5"

    It feel like it should be doable but I can't figure it out. Any help?

    John

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Formula Help for Annual Leave Tracker

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Note that the Paperclip icon does not work.

    Pete

  3. #3
    Registered User
    Join Date
    04-11-2017
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    Re: Formula Help for Annual Leave Tracker

    Good idea, thanks Pete! Attached is a sample.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula Help for Annual Leave Tracker

    Hi

    you could try:

    Please Login or Register  to view this content.
    I hope you'd get soon more convenient solutions.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula Help for Annual Leave Tracker

    Hi again,

    formula recognises "leave 7.5" and "LeAVE 7.5"

    =IFERROR(SUMPRODUCT(AGGREGATE(15,6,SUBSTITUTE(UPPER(E2:NC2),"LEAVE","")/ISNUMBER(SEARCH("LEAVE",E2:NC2)),ROW(INDIRECT("1:"&COUNTIF(E2:NC2,"*LEAVE*"))))),0)


    Again, I'm sure there are easier ways to get same outputs.

    Regards
    Last edited by canapone; 04-11-2017 at 06:09 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Formula Help for Annual Leave Tracker

    Actually I didn't get any idea from your attachment. Can you confirm please on which cells you will write leave?

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula Help for Annual Leave Tracker

    Hi

    in the attached file I've tested some formulas in C2:C9

    Hope it helps

  8. #8
    Registered User
    Join Date
    04-11-2017
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3
    Thanks!

    I didn't do myself any favours by making it needlessly busy and complicated. Thanks everybody
    Last edited by AliGW; 04-11-2017 at 11:56 AM. Reason: Unnecessary quotation removed.

  9. #9
    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,368

    Re: Formula Help for Annual Leave Tracker

    You can say that again!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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.

  10. #10
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula Help for Annual Leave Tracker

    Hi AliGw, hi Roquet_man

    I guess I've unnecessarily complicated the formula. I don't have the complete picture of job.

    Some examples: if E2:NC2 houses only dates and text like "LEAVE 1/2.5/ ...24) you could tighten the formula.

    =SUMPRODUCT((SUBSTITUTE(UPPER(E2:NC2),"LEAVE","")<25)*SUBSTITUTE(UPPER(E2:NC2),"LEAVE",""))


    If you could type only the numbers in order to record the Leaves, formula could be totally dehydrated (dates after 1/1/2000 are numbers > 35000)

    =SUMIF(E2:EN2,"<25")

    just for sharing other ideas
    Last edited by canapone; 04-11-2017 at 02:17 PM.

+ 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 to add sickness/annual leave in sheet
    By dougers1 in forum Excel General
    Replies: 21
    Last Post: 04-09-2016, 06:49 AM
  2. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  3. Sick leave annual leave minutes spreadsheet
    By News12kim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-22-2013, 03:28 AM
  4. Creating a Annual Leave tracker spreadsheet
    By char12 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-06-2012, 02:52 PM
  5. Annual leave accrual formula
    By Lebogang in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-08-2012, 07:56 AM
  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. [SOLVED] employee annual sick leave tracker with hours owing not used
    By Melanie in forum Excel General
    Replies: 0
    Last Post: 07-21-2005, 02:05 AM

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