+ Reply to Thread
Results 1 to 5 of 5

COUNTIF & day/date formula help - Annual Leave Tracker

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    48

    COUNTIF & day/date formula help - Annual Leave Tracker

    I keep a tracker for the business that updates annual leave booked on a weekly and quarterly basis. This is great for people to be able to see what they've taken and what is booked but I have been asked to show a running total (based upon the figures in this file) that shows a running total up to the current week.

    I've played around with a few COUNTIF and date specific formulas (incorporating TODAY & WEEKNUM) but nothing seems to just work and show me the figure, up to the previous week, in cell N6. It should be showing 2196 and would update weekly adding in the next weeks figures (as these can be updated at short notice but need to be done before the next week starts)

    Is this possible given the layout/format of the data and if so can anyone help?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: COUNTIF & day/date formula help - Annual Leave Tracker

    Does this work for you?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    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: COUNTIF & day/date formula help - Annual Leave Tracker

    Hi,

    Since today is 6 January then presumably the result you require is 2203, i.e. 2196 plus 7 for the current week. In which case

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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.

  4. #4
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: COUNTIF & day/date formula help - Annual Leave Tracker

    Thanks both, PaulSP8 that worked perfectly

    Richard, the figures and representative dates are weekly totals so that would include potential dates that haven't happened yet. It still actually works for something else I might do in regards to forecasting so thank you too

  5. #5
    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: COUNTIF & day/date formula help - Annual Leave Tracker

    Quote Originally Posted by Streaky View Post
    Thanks both, PaulSP8 that worked perfectly

    Richard, the figures and representative dates are weekly totals so that would include potential dates that haven't happened yet. It still actually works for something else I might do in regards to forecasting so thank you too
    The formula I gave you has a condition that the values chosen should be less than or equal to the current date. It's essentially the same as Paul's. The only difference is mine is retricted to your actual data range, Paul's uses the whole of rows 2&3.

    Neither formula include any projections after today's date.

+ 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] Leave tracker - how to use formula to automate the total leave days
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2018, 10:59 PM
  2. Formula Help for Annual Leave Tracker
    By Roquet_Man in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-11-2017, 01:47 PM
  3. Annual Expiration Date Tracker
    By americanogig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2017, 01:43 PM
  4. 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
  5. Creating a Annual Leave tracker spreadsheet
    By char12 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-06-2012, 02:52 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. [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