+ Reply to Thread
Results 1 to 9 of 9

Monthly totals & change cells based off date

  1. #1
    Registered User
    Join Date
    11-29-2017
    Location
    Clearwater, FL
    MS-Off Ver
    2016
    Posts
    47

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,094

    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.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    11-29-2017
    Location
    Clearwater, FL
    MS-Off Ver
    2016
    Posts
    47

    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. #4
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,094

    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. #5
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,094

    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. #6
    Registered User
    Join Date
    11-29-2017
    Location
    Clearwater, FL
    MS-Off Ver
    2016
    Posts
    47

    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. #7
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,094

    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. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    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. #9
    Registered User
    Join Date
    11-29-2017
    Location
    Clearwater, FL
    MS-Off Ver
    2016
    Posts
    47

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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