# Monthly totals & change cells based off date

1. ## Monthly totals & change cells based off date

I have a sheet with each employee that list the entire month. At the end of the month, I want the dates to automatically change but on the sheet called "Monthly Stats" to keep the values of the month. I have included the workbook for any assistance. Also on the employees sheets I would like for it to look up the values of Email Touches & TSA Hours from Sheet 2 based on the dates (with the dates changing each month). Any help would be greatly appreciated.

2. ## Re: Monthly totals & change cells based off date

Somehow the file upload is not working right now. So here are two formulas to update Email Touches and TSA Hours into individual sheets from Sheet2.

Email Touches:
HTML Code:
``=INDEX(Sheet2!\$A\$1:\$NC\$49,MATCH(\$A\$1,Sheet2!\$A\$1:\$A\$49,0),MATCH(\$A3,Sheet2!\$A\$4:\$NC\$4,0))``
TSA Hours:
HTML Code:
``=INDEX(Sheet2!\$A\$1:\$NC\$49,MATCH(\$A\$1,Sheet2!\$A\$1:\$A\$49,0)+2,MATCH(\$A3,Sheet2!\$A\$4:\$NC\$4,0))``
You can copy these to each individual sheet in C3 and D3 and copy down to end of month.

Hope this works for you.

3. ## Re: Monthly totals & change cells based off date

That is perfect - now is there a way that when the month rolls to the next month - the dates on the employees sheet will automatically change. But I need those numbers to remain in the last months cells on the Monthly Stats sheet and start the running total for the next month on the monthly stats sheet?

4. ## Re: Monthly totals & change cells based off date

You already have a formula in A3 on employee sheets. That will show first of the current month and subsequently down the column all dates for the month are calculated. Everything else you ask for is already taking place. For the dates in column A [on employee sheets], if you want to make it accurate formulas should be modified to accommodate for no of days in different months [28, 29, 30 or 31] depending on month and year. You can search for that in this forum and I'll see if I can figure it out as well.

5. ## Re: Monthly totals & change cells based off date

Can't seem to upload file, so here are three formulas you need to enter in A31, A32 and A33.
They will take care of leap year, February and other months with 30 days.

A31:
HTML Code:
``=IF(AND(MONTH(A30)=2,DATE(YEAR(A30),3,0)-DATE(YEAR(A30),2,28)=0),"",A30+1)``
A32:
HTML Code:
``=IF(A31="","",IF(MONTH(A30)=2,"",A31+1))``
A33:
HTML Code:
``=IF(A32="","",IF(OR(MONTH(A30)=4,MONTH(A30)=6,MONTH(A30)=9,MONTH(A30)=11),"",A32+1))``
You'll have to copy these on all employee sheets.

6. ## Re: Monthly totals & change cells based off date

I'm sorry, I wasn't clear with my question. On the Monthly stats sheet, under errors, I have it count the errors for that month. What I would need is that after that month ends, it will start to count for the next month without changing the previous month's totals.

This is my current formula on the Monthly Stats sheet:
``Please Login or Register  to view this content.``

7. ## Re: Monthly totals & change cells based off date

I see what you need. I don't know of any way to do that without using VBA. Maybe someone else knows how.
Basically, you want to look at Month on the employee sheet column A and either calculate error total or leave previous value.
If you are ok with VBA solution, let me know.

8. ## Re: Monthly totals & change cells based off date

Other than using VBA, you have the option of recording a year's worth of data on each employees sheet.

It is then very easy to produce monthly summary reports and no data is "lost".

9. ## Re: Monthly totals & change cells based off date

Well it needs to only show monthly stats so if you know the way for VBA I am all ears! Thank you for your help!

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