Posts by cthompson73

    That did help. And I did figure out what my issue was.


    The MID() function that I was using to extract the date from the text string was storing it as TEXT even though I formatted the cells as DATE. I ended up finding the DATEVALUE() function which converts a date from text to the serial that Excel recognizes as a date. Once I did that, it started working!


    Thanks for the assist!

    Ok, that does make sense and thanks for pointing that out. I did notice that the date column that you added in looks to be just the date typed in and formatted as 'Date'.


    Since the data I'm pulling is cut/pasted from the website, it's all just text values. So I was using the MID() function in the Date2 column to extract the date from Column 3. I tried tinkering with it by formatting it as 'Date' but that didn't work. I also tried adding a Date3 column formatted as 'Date' and pasting the values from Date2 into it.


    So it looks like it just has to be typed in. Which will be difficult as the actual data is 1000's of records. I'll have to see if I can find a workaround for that, but thanks for the response and fix!

    So, I'm trying to build a call tracker for myself to help track my calls but I'm having trouble with the COUNTIFS function. All the searches on Google were helpful and the formula looks right but it's not returning the correct value.


    I have three named ranges that reference to table data. CALLCAMPAIGN, CALLDATE, and CALLDISPO (this last one isn't used yet as I'm stuck figuring out this issue).


    The formula to pull by a single staticly set date works fine - =COUNTIFS(CALLCAMPAIGN,$A5,CALLDATE,B$1)


    But I want to have two cells that contain the Start Date and End and have the formula count all the calls between those dates. The formula I've tried is


    =COUNTIFS(CALLCAMPAIGN,$A5,CALLDATE,">="&$F$1,CALLDATE,"<="&$F$2)


    But instead of returning 1 for the date range I put, it is returning 0 and I can't figure out why. Any suggestions? File is attached.

    Both of these responses were fantastic! I thank you both for the assistance.. Now I'm going to go sift through the code on both so I can understand how it's working.

    I used to be pretty good at VBA coding but then I took a hiatus and went to chase a dream. Was away from computers for several years and kinda forgot things. But, just like riding a bike I'm sure it will come back the more I dig into it. :)


    Thanks again guys!

    Hey all,


    I'm trying to find a way to create some VBA code to help clean up and organize some data so I can create some reports. The data gets pulled in this format:


    Call
    123456789
    123456789
    Sun, 12/19/2021, 2:35 pm
    0:02
    0:09
    Client Name here
    A No Answer.
    Call
    123456789
    123456789
    Sun, 12/19/2021, 2:35 pm
    0:02
    0:09
    Client Name here
    A No Answer.


    It's call data, and each set of call data is 8 rows, starting with the "Call" row. What I'm trying to do is copy the 7 rows of data below "call" and then paste them in the cell next to "Call" transposed so they string out across the columns. I've tried recording a macro ("TestMacro") of what I was doing, but it puts specific cell references in the code and doesn't work if I rerun the macro.


    I thought I might be able to use ActiveCell and Offset to try to select the data but that doesn't seem to want to work either. Ideally, I'd like to create code that will go through and copy/transpose each set of data into it's own row and cycle through all data until it reaches EOF. Some of these data files are in excess of 500+ calls.


    I've attached a sample workbook with a few sample records on Sheet1, and how I'd like everything to end up on Sheet2. If someone could point me in the direction of the right VBA commands to use I can try to write the code myself. I'm just not finding any help on Google and this was the first forum I came across on a search for Excel 2019 VBA forums.


    Thanks!