Posts by JohnUKUKUK

    Re: shift-rht, insrt cell above, dwn, shift-rht, copy, up, shift-rht, past-formatting


    Thank you. I understand what you say about my requirement to get the code tidied up so it doesn't only work for the cells I've selected while recording the macro, but please understand, I was trying to follow the rules. I searched for a similar thread, then I posted exactly what I wanted. I didn't specifically want a tidied up recorded macro, as a newly written macro would have done just as well, so I didn't put that in my request for help. I thought perhaps it would save confusion if I just requested the end goal rather than posting my failed attempt and asking for someone to help fix it.


    The code is:


    Exactly what I'm trying to do as I would do it just clicking is the following:


    • shift-rht (selecting two cells)
    • insrt cell above (moving the two cells I've selected down one)
    • dwn (moving active cell back to the first two cells to copy them)
    • shift-rht
    • copy
    • up (moving back to the new cells above to paste the formatting)
    • shift-rht
    • past-formatting


    I would like to do this using a macro.


    Thank you for your help.

    [TABLE="width: 500"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Unresolved

    [/td]


    [/tr]


    [tr]


    [td]

    13/08/2013

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14/08/2013

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    15/08/2013

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    15/08/2013

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]



    Hi, I am looking for a formula that returns the oldest date from the date column if unresolved=yes. So, with the table above the result would be 14/08/2013.


    Thank you in advance for your help!

    Re: NETWORKDAYS and SUMPRODUCT


    Quote from NBVC;671481

    I don't see the "amended formula" in your attachment.


    No it isn't there - it is

    =SUMPRODUCT(--(NETWORKDAYS(Issues!$A$2:$A1009+0,Issues!$B$2:$B1009+0)>1),--(NETWORKDAYS(Issues!$A$2:$A1009+0,Issues!$B$2:$B1009+0)<=1),--(WEEKNUM(Issues!$A$2:$A1009+0)=$A8))


    and it goes into cell E8 in sheet "Weekly Report" but it doesn't work

    Re: NETWORKDAYS and SUMPRODUCT


    And to clarify - I already have a code telling me how many issues were resolved the same week between 1 and 2 days, as you will see in the attached. But I would like a code for working days that count issues that start on one week and end on another.

    Re: NETWORKDAYS and SUMPRODUCT


    Quote from NBVC;671456

    First of all, did the first formula work? Was it what you intended?


    The code did not work. The code you provided was for cell E8 in sheet "Weekly Report" the attached - your code has been amended for the attached below:


    =SUMPRODUCT(--(NETWORKDAYS(Issues!$A$2:$A1009+0,Issues!$B$2:$B1009+0)>1),--(NETWORKDAYS(Issues!$A$2:$A1009+0,Issues!$B$2:$B1009+0)<=1),--(WEEKNUM(Issues!$A$2:$A1009+0)=$A8))


    forum.ozgrid.com/index.php?attachment/54599/


    Basically, I am aiming to have a code that does not rely upon a helper, which tells me for each week of the year how many issues arose that week, that were resolved within 2 working days, even when those days spill over into the next week.

    Re: NETWORKDAYS and SUMPRODUCT


    Quote from NBVC;671292

    but I am not understanding what you mean for the second.. can you give an example of what you mean and what you expect?



    Sure, so say I have a list of dates when the issue was raised and when it was responded to:


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Issue date

    [/td]


    [td]

    Response date

    [/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 118"]

    [tr]


    [TD="class: xl65, width: 118, bgcolor: #DCE6F1"]25/06/2013 12:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Tuesday)[/TD]

    [td]

    [TABLE="width: 115"]

    [tr]


    [TD="class: xl67, width: 115, bgcolor: #DCE6F1"]25/06/2013 13:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Tuesday)[/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 118"]

    [tr]


    [TD="class: xl66, width: 118, bgcolor: #DCE6F1"]25/06/2013 12:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Tuesday)[/TD]

    [td]

    [TABLE="width: 115"]

    [tr]


    [TD="class: xl67, width: 115, bgcolor: #DCE6F1"]26/06/2013 13:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Wednesday)[/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 118"]

    [tr]


    [TD="class: xl66, width: 118, bgcolor: #DCE6F1"]26/06/2013 12:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Wednesday)[/TD]

    [td]

    [TABLE="width: 115"]

    [tr]


    [TD="class: xl67, width: 115, bgcolor: #DCE6F1"]26/06/2013 13:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Wednesday)[/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 118"]

    [tr]


    [TD="class: xl66, width: 118, bgcolor: #DCE6F1"]28/06/2013 12:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Friday)[/TD]

    [td]

    [TABLE="width: 115"]

    [tr]


    [TD="class: xl67, width: 115, bgcolor: #DCE6F1"]01/07/2013 13:00

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    (Monday the next week)[/TD]

    [/tr]


    [/TABLE]


    From the above table we can see the following:

    • that the first and third issues were responded to in 1 hour, so less than 1 working day.
    • the second and fourth issues were responded to in 1 working day and 1 hour, so greater than 1 day and less than or equal to 2 days.
    • but the fourth issue is different to the second in that it wasn't responded to the same week.


    The result I would like to get is the following:


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Start of week

    [/td]


    [td]

    Issue week no.

    [/td]


    [td]

    Number of issues responded to in 1 working day

    [/td]


    [td]

    Number of issues responded to between 1 and 2 working days (>1 and >=2)

    [/td]


    [/tr]


    [tr]


    [td]

    23/06/2013
    (Sunday)

    [/td]


    [td]

    26

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [/TABLE]



    Am I right in saying that the formula as it stands will give me this:


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Start of week

    [/td]


    [td]

    Issue week no.

    [/td]


    [td]

    Number of issues responded to in 1 working day

    [/td]


    [td]

    Number of issues responded to between 1 and 2 working days (>1 and >=2)

    [/td]


    [/tr]


    [tr]


    [td]

    23/06/2013
    (Sunday)

    [/td]


    [td]

    26

    [/td]


    [td]

    2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/TABLE]


    Thank you for your help.

    Hi,


    The formula tracks how many issues have been responded to in more than 1 day and less than (or equal to) 2 days of the issue being raised, for any given week. The weeks are set to start on Sunday and end on Saturday:


    =SUMPRODUCT(--(Issues!$F$2:$F1000-Issues!$E$2:$E1000>1),--(Issues!$F$2:$F1000-Issues!$E$2:$E1000<=2),--(WEEKNUM(Issues!$E$2:$E1000+0)=$A8))


    Columns Issues!F and Issues!E are date columns. Column A just has week numbers 1 to 52.


    Now, I reckon this is going to be tricky (well done in advance to the person who can figure it out!):


    The question is this: how would I code this so that it counted the number of issues that have been responded to between 1 and =2 working days, for any given week, BUT ALSO track those issues that were responded to between 1 and =2 working days that were raised on the given week but were not responded to until the next working week? (I may also want to add a range for holidays). Can this be done?


    Thank you.

    Re: Getting rid of helper columns


    Quote from NBVC;670859

    =SUMPRODUCT(--(Queries!$F2:$F5000-Queries!$E2:$E5000>1),--(Queries!$F2:$F5000-Queries!$E2:$E5000<=2),--(WEEKNUM(Queries!$E2:$E5000+0)=A8))


    As it happens I tried this formula prior to posting, but I missed out a comma and it didn't work. Thanks for your help!

    Re: Getting rid of helper columns


    Quote from NBVC;670665

    =SUMPRODUCT(--(Queries!$F2:$F5000-Queries!$E2:$E5000<=1),--(WEEKNUM(Queries!$E2:$E5000+0)=A8))


    Thank you for this. It worked well. Would you tell me how I might do greater than 1 and less than or equal to 2 with the above?

    Currently I have pretty much the following:


    =(SUMIF(Queries!$J:$J,$A8,Queries!O:O))



    • where cells in column Queries!J give the week number of the date in column Queries!E such as: =WEEKNUM(E3,1)
    • where A is just has the week number for all 52 weeks
    • and where Queries!O is:=IF(($F7-$E7)>1,"",1). - Queries!F and Queries!E columns are dates.


    Basically, Queries!O and Queries!J are helper columns.


    The question is, how can I compact all this into a single string of code so that I don't need to use helper columns?

    Re: SUMIFS and WEEKNUM


    Quote from AAE;670274

    If you convert the data range into an Excel Table all formulas and formatting will automtically extend to new rows that are added to the table. Plus, you get the benefit of using structured references in your formulas.


    Thank you.

    Re: SUMIFS and WEEKNUM


    Excellent. That worked very well. Thank you. Decided to go with:
    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$3:$E$1000+0)=A8),Sheet1!$H$3:$H$1000)
    The problem with the other method is that helper column. When new rows are added I would need to drag the formula down again - is that right? I'm using Office 2010.


    Secondary question - do you know how I'd do the same thing with count rather than sum? (I'm not familiar with sumproduct, as you can tell)

    I would like to do this:


    IF WEEKNUM of Sheet1!E:E = A5
    THEN SUM Sheet1!H:H


    So far I have this:



    =SUMIFS(Sheet1!H:H,WEEKNUM(Sheet1!E:E,1),A8)


    It isn't working.


    Please help. Thank you.