+ 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
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    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: 
    TSA Hours:
    HTML Code: 
    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
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    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
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    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: 
    A32:
    HTML Code: 
    A33:
    HTML Code: 
    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
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    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)

Similar Threads

  1. VBA to make a weekly & monthly totals after cell change
    By jamesraywebber in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2018, 05:17 PM
  2. Running monthly totals based off a weekly adding
    By jamesraywebber in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2017, 11:02 PM
  3. Running totals based off monthly
    By jamesraywebber in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-16-2017, 07:45 PM
  4. Replies: 3
    Last Post: 02-12-2011, 12:10 PM
  5. Sum Monthly totals by date
    By Nomad445 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2008, 01:55 PM
  6. [SOLVED] Template to track weekly & monthly totals, by day & date?
    By JKPierce in forum Excel General
    Replies: 0
    Last Post: 04-06-2006, 11:45 AM
  7. How do I sum YTD totals based on monthly totals
    By Bsgrad02 in forum Excel General
    Replies: 3
    Last Post: 07-12-2005, 12:05 PM

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