+ Reply to Thread
Results 1 to 5 of 5

Values based on a rolling 6 month time period

Hybrid View

andyking40 Values based on a rolling 6... 11-26-2018, 09:47 AM
etaf Re: Values based on a rolling... 11-26-2018, 10:14 AM
andyking40 Re: Values based on a rolling... 12-17-2018, 11:31 PM
etaf Re: Values based on a rolling... 12-18-2018, 03:20 AM
AliGW Re: Values based on a rolling... 12-18-2018, 03:22 AM
  1. 11-26-2018, 09:47 AM #1
    andyking40 is offline
    Registered User
    Join Date
    05-01-2012
    Location
    Gibraltar
    MS-Off Ver
    Excel 365
    Posts
    38

    Values based on a rolling 6 month time period

    Hi Experts

    I work in a Call Centre and have created a sheet for tracking Agent hols, absence etc.

    I would like the following values to be based on a rolling 6 month timespan:
    • WHS,
      Sick days,
      Sick instances,
      Sickness score,
      Auth absences,
      Unauth absences,
      Late 0.5 absences,
      Late 1 instances,
      Total late score.

    So for example if an Agent has 1 instance of sickness on 1st Jan the sickness score would be 1. If there was no more sickness during the next 6 months, the score would revert to 0 on 1 July.

    I've tried to work it out by looking at Google, but it's beyond my capability.

    Would really appreciate some help if anyone out there could take a look - hopefully it's attached.

    Many thanks in advance

    Andy
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-26-2018, 10:14 AM #2
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,108

    Re: Values based on a rolling 6 month time period

    quite a large spreadsheet - so rather than work through that - perhaps a small sample or explanation

    you can do a rolling 6 mths using TODAY(), when you open the sheet

    assuming on calendar sheet
    Sickness - you are adding instance and days - I assume again -
    using
    =COUNTIF(Table2[@[Column2]:[Column367]],"SCs1")+COUNTIF(Table2[@[Column2]:[Column367]],"DCs1")
    maybe not as i can not enter "SCs1"
    but something like
    COUNTIFS ( ...... , date range ,,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
    Not i have -6 on the months to go back 6 months from today
    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.
    Reply With QuoteRegister To Reply
  3. 12-17-2018, 11:31 PM #3
    andyking40 is offline
    Registered User
    Join Date
    05-01-2012
    Location
    Gibraltar
    MS-Off Ver
    Excel 365
    Posts
    38

    Re: Values based on a rolling 6 month time period

    Thank you -works!
    Reply With QuoteRegister To Reply
  4. 12-18-2018, 03:20 AM #4
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,108

    Re: Values based on a rolling 6 month time period

    you are welcome
    Reply With QuoteRegister To Reply
  5. 12-18-2018, 03:22 AM #5
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,539

    Re: Values based on a rolling 6 month time period

    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Attendance Tracker with a rolling six month period?
    By taylorsm in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-16-2019, 03:04 AM
  2. Formula for calculating 12 month rolling period
    By Stroodle in forum Excel General
    Replies: 1
    Last Post: 06-08-2016, 09:42 AM
  3. 12 month rolling period with variable dates over the 12 month period
    By ChrissyP2 in forum Excel General
    Replies: 10
    Last Post: 01-09-2014, 07:04 PM
  4. [SOLVED] Automatically updated rolling 24 month period
    By jrholden in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-05-2013, 11:04 AM
  5. Criteria Satisfied in Rolling 6 Month Period
    By jasonmitnick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-27-2012, 01:13 PM
  6. How to count dates in a 12 month rolling period
    By butlej6 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-05-2008, 06:00 PM
  7. How do I count dates in a 12 month rolling period?
    By butlej6 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2008, 05:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1