# Calculate points for year based on last 180 days

1. ## 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.

2. ## 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.

3. ## 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.

4. ## 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. ## 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. ## 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.

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

#### 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