+ Reply to Thread
Results 1 to 13 of 13

Calculate based on Date

  1. #1
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Calculate based on Date

    I originally asked this on the Microsoft Community Forums this morning, but it never got looked at past me checking on it.

    I have a spreadsheet to track attendance instances for my team at work. Our attendance policy uses a points system to determine disciplinary actions and allow some flexibility for unforeseen absences. The policy indicates that if you do not have two occurrences within 30 days of each other, the first rolls off after 60 days, but if you do have a second occurrence within 30 days, it stays in place for 6 months. I need a formula that will calculate this properly.


    My current sheet and formulas looks like this:

    A B C D E F G H
    1 Date Absence Code Points Accrued Total Points Roll-Off Date Days to Rolloff Action Required Notes
    2 =IF(B2="","",VLOOKUP(B2,Guidelines!$B$4:$C$10,2,FALSE)) =IF(C2="","",C2) =IF(OR(AND(A2>0,C3=""),AND(A2>0,C3=0,C4=0),AND(A2>0,C4="")),A2+60,IF(OR(AND(C3=0,C4>0),AND(A2>0,C3>0)),A2+182,"")) =IF(E2="","",IF(AND(A2>0,C2>0),IF(E2-TODAY()>0,E2-TODAY(),0),"")) =IF(D2="","",IF(D2=0,"N/A",VLOOKUP(D2,Guidelines!$E$4:$F$23,2,FALSE)))
    3 =IF(B3="","",VLOOKUP(B3,Guidelines!$B$4:$C$10,2,FALSE)) =IF(C3="",D2,D2+C3) =IF(OR(AND(A3>0,C4=""),AND(A3>0,C4=0,C5=0),AND(A3>0,C5="")),A3+60,IF(OR(AND(C4=0,C5>0),AND(A3>0,C4>0)),A3+182,"")) =IF(E3="","",IF(AND(A3>0,C3>0),IF(E3-TODAY()>0,E3-TODAY(),0),"")) =IF(D3="","",IF(D3=0,"N/A",VLOOKUP(D3,Guidelines!$E$4:$F$23,2,FALSE)))

    The Absence Code is a Data Validation list. The formulas in Column E are the ones that I am looking to change. They need to be based on the dates in Column A, and only change if the Points Accrued is greater than 0 in Column C of the same Row identified in Column A.
    Last edited by JL1213; 10-22-2019 at 04:19 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Calculate based on Date

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Calculate based on Date

    Please also tell us on which site(s) precisely you have cross-posted so that the links can be added ( you cannot do this yourself yet).
    Please read our forum rules to understand why this is a must.
    Thanks

  4. #4
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Re: Calculate based on Date

    This was previously posted in https://answers.microsoft.com/en-us/...c-4c9a8655f6d4
    Edit: Replaced the file due to prior issues.
    Last edited by Pepe Le Mokko; 10-24-2019 at 02:15 AM. Reason: Added link. Thanks for complying

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Calculate based on Date

    We may be able to offer some help if we can see some scenarios in which the existing formulas are not yielding the proper results.
    It may also be helpful to see the Guidelines sheet.
    Pepe's post (#2) details how to upload a sample.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Re: Calculate based on Date

    Sorry for the delay in getting this uploaded. I have been super busy and this is the first chance I have had. I have about 8 lines set up as best as I have found, but it currently is checking only 2 lines rather than comparing the entier columns, so it will get the calculations wrong for how many days something stays on.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Calculate based on Date

    "I have about 8 lines set up..."
    There isn't any data on the "Jonathan" sheet which is where I assume the "8 lines" are supposed to be.

  8. #8
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Re: Calculate based on Date

    I have filled in some generic information to show how the sheet is calculating incorrectly. A new point accrual witin the 30 days following the last occurance result in a 6 month wait for rolloff rather than a 60 day rolloff. Excused absences do not cause this change. In the attached example, there was an unexcused late arrival, 2 doctor's notes, and another unexcused late arrival. If you change either of the doctor's notes to a late arrival, it will change Row 2 to show 6 months rather than 60 days, which is correct.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Calculate based on Date

    I have attempted to open the file attached to post #8 (or even save it to my hard drive) without success. Keep getting a message that states that either the format or file extension are not valid. I recommend that you attach another copy to a new post so that we will receive notification.
    Note that I can open the file attached to post #6 so suggest you use that same file format/extension.
    Let us know if you have any questions.

  10. #10
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Re: Calculate based on Date

    Added 4 rows worth of information. First two columns are all that need be edited. The formulas in the other columns work based on those two. Feel free to change the data in the third entry row second column to anything not excused to see how it calculates differently in the sheet.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Calculate based on Date

    If I understand correctly then pasting the following into cell E2 on the Jonathan sheet (and copying down) should do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  12. #12
    Registered User
    Join Date
    10-22-2019
    Location
    South Carolina, USA
    MS-Off Ver
    365 | 2016
    Posts
    42

    Re: Calculate based on Date

    I just tested it and that seems to work perfectly. I do have one question regarding the countifs statement. Since that is an absolute cell value at the end for A$8, if the sheet is going to go to say A366 (Which I believe this is going to in the end), would I need to update the countifs to reflect the last cell in the sheet?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Calculate based on Date

    Yes, the formula could be changed to read: =IF(A2="","",IF(AND(COUNTIFS(A3:A$366,"<"&A2+30,C3:C$366,">0"),C2>0),A2+182,IF(C2>0,A2+60,A2)))
    If this resolves the issue then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Calculate Headcount by department, based on Hire date and Termination date
    By Cornelia in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-11-2020, 07:42 AM
  2. Replies: 3
    Last Post: 06-23-2015, 09:56 AM
  3. Calculate next due date based on start date frequency and current date
    By ironoverload in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-09-2014, 07:08 AM
  4. Replies: 2
    Last Post: 10-06-2013, 02:03 PM
  5. Replies: 0
    Last Post: 03-21-2013, 12:22 PM
  6. Calculate ship date based on date and time order is received
    By joekomar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2013, 10:57 PM
  7. Calculate START date based on duration (work hours) and END date
    By kaaver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2010, 12:21 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1