+ Reply to Thread
Results 1 to 6 of 6

Calculate points for year based on last 180 days

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Calculate points for year based on last 180 days

    I have a spreadsheet for my employees to calculate attendance points for the year. My 2007 excel doesn't have enough columns to put everything on one tab so I have each month on a separate tab and a 13th tab that calculates the points per month. What I need next is a way to calculate the points based on the last 180 days because points drop off after 6 months. Attached is my spreadsheet if anyone has any ideas that may help me.

    It'll also have to calculate whent he year changes too.

    Thanks in advance!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Calculate points for year based on last 180 days

    @Diogie

    A couple of suggestions.

    Don't work with merged cells (e.g B4)

    You get in trouble with it sooner or later.


    Please Login or Register  to view this content.
    How many columns your gonna use, since there are more columns than you used?


    Why not all days, for the whole year, input in column E? (in rows)

    The advantance is you have all data on 1 sheet.

    Another advantance is you can easy count / sum / pivot table etc. with the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    Northern CA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculate points for year based on last 180 days

    Diogie, Oeldere is correct, you should avoid merging cells whenever possible. I recommend using their suggestion to move time vertically on one worksheet, as on "New Sheet" in the attached workbook.
    I tried to build a pivot as an example, but the file is not working like my own file; not sure if it was corrupted during transfer. But, I wrote a "countifs" statement to help you on your way to gathering the details into your summary.

    Hope this works for you.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-01-2012
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate points for year based on last 180 days

    Thanks so much for the help so far. I'm going to check this out more.

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate points for year based on last 180 days

    I was actually thinking the employee names could be in the rows and the dates of the year in the columns which would mean at least 370 columns total (including the column that would calculate the total number of points for the last 180 days).

  6. #6
    Registered User
    Join Date
    08-01-2012
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate points for year based on last 180 days

    I'm still struggling with this. I couldn't get the change to work but I did get the spreadsheet to expand to more columns. I'm attaching a copy of an abbreviated version of it. It does everything I need it to do except calculate the totals based on the current date and 179 days previous to the current date. I can't seem to tweek the formula correctly. Can you check it out and let me know where I've made the mistake?

    I've corrected the attachment and hopefully the entire spreadsheet opens this time.

    Thanks so much.
    Attached Files Attached Files
    Last edited by Diogie; 07-16-2013 at 02:19 PM. Reason: attachment didn't attach correctly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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