+ Reply to Thread
Results 1 to 9 of 9

Need help to easily copy rows that contain a formatted date

  1. #1
    Registered User
    Join Date
    05-21-2013
    Location
    Columbia, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Need help to easily copy rows that contain a formatted date

    Hello all,

    This is my first post to this forum but I want to introduce myself real quick to say hi. I am an Excel geek, and I absolutely love this website and all the insight I have obtained from it. Thanks in advance for helping me with this question:

    I created a spreadsheet for my boss with two worksheets: the first worksheet is data for reports we get returned from each employee and from which month. I have that formatted so that you enter the date the report was originally issued and it shows as "2013-Mar" (for example). On the second worksheet I have set up a table with a COUNTIFS function to show employees in the columns and months in the rows, so that it automatically keeps track of how many reports are returned from each employee according to what months they were originally issued.

    In the second worksheet, currently if I just copy the row and paste it on the row below, I have to manually change the date in the formula bar, which displays as "=5/1/2013". (The formula currently works so that all of the reports originally issued in May 2013 will be included in the calculation, and that is what I need). However, I am going to leave my position soon and I have to find a no-brainer way for my boss to figure out how to continue using this spreadsheet (which means I need to have copy/paste work without manual adjustments). Can anyone help me with this?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need help to easily copy rows that contain a formatted date

    I don't understand. Is it just that the format is wrong? You want it to be 2013-May instead of 5/1/2013? If so, then highlighting the column (or cells) and changing them to the format you want should work. I also don't understand why your date has or needs an equal sign in front of it.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    05-21-2013
    Location
    Columbia, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help to easily copy rows that contain a formatted date

    Quote Originally Posted by Pauleyb View Post
    I don't understand. Is it just that the format is wrong? You want it to be 2013-May instead of 5/1/2013? If so, then highlighting the column (or cells) and changing them to the format you want should work. I also don't understand why your date has or needs an equal sign in front of it.
    Thanks for your question, I was afraid I didn't explain it correctly.

    In the first worksheet (for data entry) it doesn't matter necessarily how the issued date is formatted, but in the second worksheet (summary table) I need to be able to count all the reports that were issued by month (sum from May 2013, sum from June 2013, etc) per employee. Perhaps there is a better way to do this which will make the copy/paste for each new month easier?

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need help to easily copy rows that contain a formatted date

    I think I'm getting an idea of what you are trying to do, but I'm still not confident. Maybe upload a sample sheet.

    Does the first sheet have the history of all data, or does it get reset each month? I then think you are saying the summary table has a history of each month and employee over the last xx years or since the begining of your fiscal year.

    On a related note, your boss really can't figure out to just increase the month from the previous one when the new data is pasted? If that is the problem then something like (in cell A1):
    =eomonth("1/1/2013",ROW()-1)
    will increment the date by one month every time you drag it further down. You could add something like:
    =if(today()>eomonth("1/1/2013",row()-1),eomonth("1/1/2013",row()-1),"")
    to have it only show months that have completed for the year.

  5. #5
    Registered User
    Join Date
    05-21-2013
    Location
    Columbia, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help to easily copy rows that contain a formatted date

    Quote Originally Posted by Pauleyb View Post
    I think I'm getting an idea of what you are trying to do, but I'm still not confident. Maybe upload a sample sheet.

    Does the first sheet have the history of all data, or does it get reset each month? I then think you are saying the summary table has a history of each month and employee over the last xx years or since the begining of your fiscal year.

    On a related note, your boss really can't figure out to just increase the month from the previous one when the new data is pasted? If that is the problem then something like (in cell A1):
    =eomonth("1/1/2013",ROW()-1)
    will increment the date by one month every time you drag it further down. You could add something like:
    =if(today()>eomonth("1/1/2013",row()-1),eomonth("1/1/2013",row()-1),"")
    to have it only show months that have completed for the year.
    Yes, my boss really can't figure out how to increase the month. I just explained to her how to use an apostrophe to enter case numbers that start with a 0, I'm not about to spend the rest of my time here teaching her what the formula bar does, lol. I am investigating the eomonth formulas now, those are new to me but I'm hoping that should solve my problem. I'll post another reply as soon as I know one way or another. Thanks!!!

  6. #6
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need help to easily copy rows that contain a formatted date

    Can you use this formula? =DATE(2013,ROW(A5),1) to show 5/1/2013...

    Then when they copy the row of fromulas, this will show 6/1/2013...

    Or am I thinking too simplistic?

  7. #7
    Registered User
    Join Date
    05-21-2013
    Location
    Columbia, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help to easily copy rows that contain a formatted date

    Quote Originally Posted by djapigo View Post
    Can you use this formula? =DATE(2013,ROW(A5),1) to show 5/1/2013...

    Then when they copy the row of fromulas, this will show 6/1/2013...

    Or am I thinking too simplistic?
    I'm afraid I'm over-thinking this and getting frustrated, so I'm attaching an example to make it easier.

    example.xlsx

    Sheet 1 is the data entry. Sheet 2 is the table which shows reports received... the columns are the employees, the rows are the date the reports were originally issued. What's important is to know 1) how many reports were issued each month for each employee, and 2) how many reports were issued for each employee in the past 6 months and the past 12 months.

    I truly appreciate all your help so far!

  8. #8
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need help to easily copy rows that contain a formatted date

    Hi cpeaches...

    The last term in the COUNTIFS function can be a formula... so instead of typing "=5/1/2013", simply put $A22.

    So your formula in B22 should be =COUNTIFS(Sheet1!$C:$C,B$1,Sheet1!$D:$D,$A22)

    You can then copy this formula everywhere...

    The only time you need something in quotes is if it's not the equal sign...
    So for "=5/1/2013" then you just need $A22
    If you want "<>5/1/2013" then you need "<>"&$A22
    If you want ">=5/1/2013" then you need ">="&$A22
    etc.
    I hope you can now follow...

    Let me know if you have more questions

    Thanks,
    Dennis

  9. #9
    Registered User
    Join Date
    05-21-2013
    Location
    Columbia, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Thumbs up Re: Need help to easily copy rows that contain a formatted date

    Quote Originally Posted by djapigo View Post
    Hi cpeaches...

    The last term in the COUNTIFS function can be a formula... so instead of typing "=5/1/2013", simply put $A22.

    So your formula in B22 should be =COUNTIFS(Sheet1!$C:$C,B$1,Sheet1!$D:$D,$A22)

    You can then copy this formula everywhere...

    The only time you need something in quotes is if it's not the equal sign...
    So for "=5/1/2013" then you just need $A22
    If you want "<>5/1/2013" then you need "<>"&$A22
    If you want ">=5/1/2013" then you need ">="&$A22
    etc.
    I hope you can now follow...

    Let me know if you have more questions

    Thanks,
    Dennis
    Umm wow. You are right, all I needed to do was type in the cell number rather than the date in quotes. I swear I had originally set it up that way and it wasn't working so I was convinced I had to do something more complicated, but I guess I fixed something along the way.

    I truly appreciate all your help!

    (feeling like a dummy, but so grateful!)

+ 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