Posts by cs566

    Hi Glenn,

    Hope you are still doing well. Praying for your Prime Minister...


    I need help again. Hoping you might be able to assist.


    I now need to calculate the MIN date and the formula you provide (with SUMPRODUCT) doesn't seem to work when trying to find MIN vs MAX.


    On the attached:

    RawData tab has new Column for A - "Calculated New Value"

    This captures any value in the "New Value" column to show "eMotive" if equal to "Jennifer Reed", "App - Premium Offer", or "App - Non Premium Offer".

    I now need to capture the FIRST date (or MIN date) of the "Edit Date" field if "Calculated New Value" = "eMotive".


    The formula would be entered in the StatTbl tab/Column C (eMotive).



    When the formula in StatTbl tab/Column C (eMotive) has:

    =IF(SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

    The eMotive date is populated.


    If formula in StatTbl tab/Column C (eMotive) has:

    =IF(SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

    The eMotive date is BLANK


    I have done some reading and read that the MIN and SUMPRODUCT don't always play nice. Everything I have tried does not work. Any ideas?

    Thanks (again...) for your help.

    Take care.


    see attached

    Finding MIN Date.xlsx

    Hi Glenn,

    I have another question regarding the solution you provided.


    In the formula

    =SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[New Value]="Tech Review")*(RawData[Edit Date])))


    How would I revise the formula if I am looking for more than one value in the red text above. Meaning, the New Value could be "Tech Review", "Customer Related", "Not Customer Related" ?


    Thanks in advance for any assistance.

    Hope you and family still doing ok through this crazyness!

    /cristy

    Good Afternoon,

    Sorry for delay...


    Yes, I did and it works. Thank You!


    Now, when the formula finds doesn't find a match it provides a date of 1/0/1900. I want those to be blank but cannot get any IF statement to work.

    Any ideas?

    Thanks so much

    Hi,

    I am needing to find the 'MAX' date in the 'Edit Date' column when the 'New Value' column is equal to 'Tech Review'.

    There are two case numbers that have two different dates for that scenario and I need to capture the latest or 'MAX' date vs the first date.


    on my StatTbl tab I have the formula in column D:


    {=INDEX(RawData[Edit Date],MATCH(1,("Tech Review"=RawData[New Value])*([@[UniqueCase'#]]=RawData[Case Number]),0))}


    I cannot figure out how to grab the MAX of the value I am trying to populate in that cell.

    Any help would be appreciated.


    see attached

    Finding MAX Date.xlsx

    Hi,

    In the attached file, I need to create a macro that will do the following:


    1.

    Insert column between

    Sales Stage (Column B) and Estimated Close Data (column C)

    Name new Column 'Estimated Close Year'

    enter =Year(D2)

    Auto fill down (REGARDLESS OF NUMBER OF ROWS)


    2.

    Insert column between

    Estimated Close Date (column D) and Quote (column E)

    Name new column 'Qrt'

    enter formula ="Q"&ROUNDUP(MONTH(D2)/3,0)

    Auto fill down (REGARDLESS OF NUMBER OF ROWS)



    The rows of this data will change, so the macro needs to capture ALL Rows.


    Thanks in advance for any assistance.

    /c

    I have a macro that I recorded to format a dataset. Each time the dataset is generated, the bottom row of data will change.
    My recorded macro has hard-coded the formula to the last row when the macro was recorded. I need help on re-writing this to capture the column from the 2nd row and down (regardless of last row number). I have tried a couple of revisions, but continue to get a debug error.


    I have attached the data set and below is the Macro. The large, bold, italicized lines are the two selections that need revision.



    Thank you. The Start Week formula is calculating properly. The End Week formula is reflecting same date/time.
    Below represents column D with formula.
    I am needing these to show a TIME of 0:00:00
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 135"]Midnight Saturday End Week[/TD]

    [/tr]


    [tr]


    [td]

    01/13/2019 0:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 7:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 7:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 2:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 7:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 0:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 7:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 4:00:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 0:29:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 7:15:00

    [/td]


    [/tr]


    [tr]


    [td]

    01/13/2019 6:30:00

    [/td]


    [/tr]


    [/TABLE]

    The data attached represent a users START and END Time. (A & C). These are systematic date/time stamps.




    I am needing a way to calculate how to find MIDNIGHT of the SATURDAY following the Start Time (column B) and the previous MIDNIGHT of the SATURDAY of the End Time (column D).




    Many of these shifts start one week and end on another. I am wanting to separate what is actually worked in each week.




    Midnight Saturday (Start Week) - Start time (column E)


    Midnight Saturday (End Week) + End Time (column F)




    I can calculate total hours worked, but need to split these type shifts to 'bucket' hours in the respective week to another analysis.


    Any help would be greatly appreciated.
    Thanks

    I am so sorry... you are correct. I didn't update the data source like I thought I had.
    Everything good for now.
    You rock!


    It was a long day for me yesterday, so again, my apologies!
    Thanks again for your help!
    /cristy

    this post has attached worksheet
    -------------------
    I am still having problems with the ARRAY formula.
    It worked on the attachment I was sent on Monday, but now I have to go back and calculate previous months data using same table source and the formulas aren't working.


    I am wanting to calculate the MIN and MAX weeks each job (column A) had via a WK number field (Columns F and G)
    My formulas for column B (Minimum) and C(Maximum)are:


    {=MIN(IF($J$3:$J$5167=A3,IF(ISNUMBER($F$3:$F$5167),$F$3:$F$5167)))} <-- currently in Column B
    {=MAX(IF($J$3:$J$5167=A3,IF(ISNUMBER($F$3:$F$5167),$F$3:$F$5167)))} <-- currently in Column C


    I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
    Column A are unique Job numbers
    Column J has repeated Job numbers
    Column F and G have the week number affiliated with the Job # entry in column J


    This is making me crazy Lucy....


    Thanks in advance for the help!

    I am still having problems with the ARRAY formula.
    It worked on the attachment I was sent on Monday, but now I have to go back and calculate previous months data using same table source and the formulas aren't working.


    I am wanting to calculate the MIN and MAX weeks each job (column A) had via a WK number field (Columns F and G)
    My formulas for column B (Minimum) and C(Maximum)are:


    {=MIN(IF($J$3:$J$5167=A3,IF(ISNUMBER($F$3:$F$5167),$F$3:$F$5167)))} <-- currently in Column B
    {=MAX(IF($J$3:$J$5167=A3,IF(ISNUMBER($F$3:$F$5167),$F$3:$F$5167)))} <-- currently in Column C


    I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
    Column A are unique Job numbers
    Column J has repeated Job numbers
    Column F and G have the week number affiliated with the Job # entry in column J


    This is making me crazy Lucy....


    Thanks in advance for the help!

    Hi,
    I have a data sheet that I am wanting to calculate the MIN and MAX weeks each job had via a WK number field.
    My formulas for column B (Minimum) is:


    {=MIN(IF($J$3:$J$5167=A3,$F3:$f$5167))}


    I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
    Column A are the unique Job number
    Column J has repeated Job numbers
    Column F has the week number affiliated with the Job # entry in column J


    See attached