+ Reply to Thread
Results 1 to 7 of 7

Sickness tracker with a rolling 12 months

  1. #1
    Registered User
    Join Date
    07-04-2019
    Location
    Shropshire
    MS-Off Ver
    2010
    Posts
    4

    Sickness tracker with a rolling 12 months

    Hi!
    I have this spreadsheet which lists occasions of absence, and I'm trying to add a formula into Column G, which looks how many absences the employee has had in
    the 12 months before the date of the current absence(given in Column D)? eg cell G10 would return the value 2, but cell G2 would return the value 1. Any ideas?
    Thanks!
    Sophie
    Attached Files Attached Files
    Last edited by sophie-edge24; 07-04-2019 at 03:38 PM.

  2. #2
    Registered User
    Join Date
    07-04-2019
    Location
    Shropshire
    MS-Off Ver
    2010
    Posts
    4

    Sickness tracker with a rolling 12 months

    Hi everyone!
    I have this spreadsheet which lists occasions of absence, and I'm trying to add a formula into Column G, which looks how many absences the employee has had in
    the 12 months before the date of the current absence(given in Column D)? eg cell G10 would return the value 2, but cell G2 would return the value 1. Any ideas?
    Thanks!
    Sophie
    Attached Files Attached Files

  3. #3
    Forum Contributor jomaor1's Avatar
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: Sickness tracker with a rolling 12 months

    Hi!

    put this on G2:

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


    Pro Tip, Put this on F2:

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


    <3
    John.

    "I excel at jumping to conclusions"

  4. #4
    Registered User
    Join Date
    07-04-2019
    Location
    Shropshire
    MS-Off Ver
    2010
    Posts
    4

    Re: Sickness tracker with a rolling 12 months

    Thank you! And thanks for the tip on calculating the working days!

    Do you know what formula can be used so that any absences older than 12 months are excluded?
    I've updated the spreadsheet with an example of what I'm trying to explain, hopefully it makes sense!
    Edit: the additional row has the wrong date in - should be 2020 so that there is more than 12 months between the new absence and older periods of absence

    Thanks!
    Attached Files Attached Files
    Last edited by sophie-edge24; 07-04-2019 at 04:44 PM.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,338

    Re: Sickness tracker with a rolling 12 months

    Try this in G2, dragged down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does that give you what you expect?
    Regards,
    Aardigspook

    I've just moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  6. #6
    Registered User
    Join Date
    07-04-2019
    Location
    Shropshire
    MS-Off Ver
    2010
    Posts
    4

    Re: Sickness tracker with a rolling 12 months

    Hello Aardigspook

    Yes that's great - thank you!!

    Sophie

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,338

    Re: Sickness tracker with a rolling 12 months

    You're welcome, glad we could help and thanks for marking the thread as Solved.

+ 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. Replies: 1
    Last Post: 10-19-2018, 12:55 PM
  2. Rolling Sickness
    By OxJ in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 01-11-2017, 08:38 AM
  3. Sickness Tracker Help
    By reespring2016 in forum Excel General
    Replies: 3
    Last Post: 07-25-2016, 07:22 AM
  4. Replies: 9
    Last Post: 09-22-2015, 08:47 AM
  5. Calculating Total Sickness in a rolling 12 month Period
    By china in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-08-2015, 08:04 AM
  6. Rolling sickness tracking at work
    By dnlshllrd in forum Excel General
    Replies: 6
    Last Post: 06-19-2014, 11:20 AM
  7. [SOLVED] 90 rolling attendance tracker
    By Fuhgawz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-21-2013, 02:49 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