Posts by YuraYong

  • weekend dont show when =date-date

    Re: weekend dont show when =date-date


    hi benm,


    I hope you have read all the forum rules since this is your 1st post.


    See the attached file for the workings for something similar to what Batman has told you.
    However Batman assume that your "date" is Excel date data whereas I assume your "date" is text data.
    Whichever the case, networkdays is the function to use.


    Since you are a fast learner, happy learning :)


    All required is in the Excel help file for you to learn :)


    Good Luck & Have a Nice day!


    Yura

  • Count time intervals by date criteria

    Re: Count time intervals by date criteria


    Apeter,

    Yes if you have the discharge time you can use it at column J.

    But remember to add the date to the discharge time before you replace it to column J.
    The date added should be in excel date format ok :) so that in column J you have dd mm yy hh mm ss data.

    Glad I can help because above is just trial and error solution :P

    Anyway, your matrix also help alot because it reduce the area i need to try!

    Perhaps other who are better in mathematic can give better formula.

    Yura

  • Count time intervals by date criteria

    Re: Count time intervals by date criteria


    The idea proposed by AAE is very interesting :)
    But no time to experiment futher :)

    Please see attached :)
    i) sheet(With Adj) column L & M consist of the two sumproduct that will give you what I think you need.

    ii) In your maxtric, there is some error, namely those with zero extent hour, you forget to put 1. For example in cell G11, patient register 3:01 should be counted as 1 patient at the column G of your maxtrix sheet. (Good work for the maxtric!)

    iii) Also make sure you understand Column J formula. I have to count your patient leave from the based hour and less 1 minute.

    I am not able to visualise your original and amended sumproduct thus have no idea how to fix it :)

    The logic of my sumproduct is
    i) column L - count all registeration that fall within the hours
    ii) column M - count those extra hours that fall within the hours

    Good Luck & Have a nice day!

    Yura

  • Count time intervals by date criteria

    Re: Adding a date reference to SUMPRODUCT formula


    Apeter,

    Kindly confirm below information :
    i) each row represent 1 patient?
    ii) for 1st row of your data, the patient check in at 00:00 hrs and check out at 02:00 hrs?
    iii) so for just this patient, the interval count is 00:00hrs - 00:59hrs =>1, 1:00hrs-1:59hrs=>1, 2:00hrs-2:59hrs=>1?

    I ask because your existing sumproduct doesn't seem to provide this calculation :)

    I don't think I can come out with a simple array formula for you.

    What I have in mind is multiple helper columns.

    Yura

  • Select different ranges using IF formula

    Re: Select different ranges using IF formula


    You can try using this function at R6
    =IF(AND($M$3>=0%,$M$3<26%),L6,0)+IF(AND($M$3>=26%,$M$3<51%),M6,0)+IF(AND($M$3>=51%,$M$3<76%),N6,0)+IF(AND($M$3>=76%,$M$3<101%),O6,0)+IF(AND($M$3>=101%,$M$3<126%),P6,0)+IF(AND($M$3>=126%,$M$3<150%),Q6,0)

    Another option is to use nested "IF" function but such function have limitation of 7 layers only.
    With above formula you can have more than 7 layers :)

    Have a nice day!

    Yura

  • Creation based on day of the month

    Re: Creation based on day of the month


    Hi Salem,

    Greeting from Malaysia :)

    Like what SMC told you, volunteer need correct and concise information for a solution.

    I have tried simulate a simple populating macro for you.

    See attached.

    You need to do the following:
    i) input date in A1
    ii) make sure no blank row in table A3:B12, if require you can add more information
    but no blank row in between data
    iii) click the "Click" button in D1

    A macro will populate a list based on your sample file starting from cells D4.

    Have a nice day!

    Yura

  • Select different ranges using IF formula

    Re: need help on 'IF' formula


    I guess what you need might be a combination of array and formula(s). However until you can show all a working sample Excel file, I don't think anyone has any clear idea what you need.

    Have a nice day!

    Yura

  • Highlight Similarities & Difference Through Pivot Table

    Re: Highlight Similiarities &amp; Difference Through Pivot Table


    George,


    Do the following :
    i) Use conditional formatting (formula conditional formatting) and input conditional formatting at A4=NOT(ISNA(MATCH(A4,$C$4:$C$14,0)))
    i) Use conditional formatting (formula conditional formatting) and input conditional formatting at C4=NOT(ISNA(MATCH(C4,$A$4:$A$14,0))))


    In the conditional formatting (Home Ribbon -> Conditional Formatting -> New Rule... -> Use a formula to determine which cell to format), you can choose the format you want.


    I would like to highlight that
    i) your thread title is inaccurate, if you still can amend perhaps change to a more accurate title (for the benefit of all and i think is forum rule);
    ii) do not double post, if can delete them;


    Kindly note that above conditional formatting might not work in Excel version 2003 or earlier.


    Alternatively you might want to consider using data auto filter.
    Create a few more helper line to input the above formula which will return true or false.
    For example, in your sample file use column E, F, G, H as helper columns.
    Then you auto filter these column.
    Then you can filter those "true" row to give you easier viewing.



    Have a nice day!


    Yura

  • Extract everything after a symbol and copy to adjacent cell

    Re: Extract everything after a symbol and copy to adjacent cell


    You can :
    B1=LEFT(A1,FIND("?",A1)-1)
    C1=RIGHT(A1,LEN(A1)-FIND("?",A1))

    And do what Rob told you above.

    On taking away the suffixed "." from some data, you can consider the following (assuming that A2 is the data):
    B2=IF(ISERR(FIND(".",A2)),A2,LEFT(A2,FIND(".",A2)-1)&RIGHT(A2,LEN(A2)-FIND(".",A2)))

    And follow the same copy and fill as told by Rob.

    Have a nice day!

    Yura

  • How to export from csv into text file?

    Re: How to export from csv into text file?


    Just curious when array converted a text, it would not longer be array right? :) I understand that normally for exporting excel data to text is mostly because we need to feed it to other system and I am not aware of any other system that feed on 'text array'.

    Kindly ignore me if I am out of line.

    And icer6 do attached a sample file of your original 'before export' excel and 'after export' text file which will give all clear idea of what you need. :)

    Have a nice day!

    Yura

  • Sum cell numbers only in a cell with numbers and text?

    Re: Sum cell numbers only in a cell with numbers and text?


    I would suggest a helper column. Say all your data is from B1 to B10. You can use column C as helper column. In C1 you input this formula:
    =VALUE(LEFT(B1,FIND(".",B1)-1))
    Then copy and fill C1 formula to C10. Then C11 you put a sum for C1 to C10.


    Basically what I have done is covert your text to value then sum it.


    Yura

  • Microsoft Excel cannot access the file, compile via VBA

    Re: Microsoft Excel cannot access the file, compile via VBA


    vapid,


    I have made some additional:
    i) add 'error trap' in the macro (If any error, macro will still continue);
    ii) change normal open to read only open (in case in your server the sharing is only 'read-only')
    iii) add directory information for the "Dir" method in Excel vba (it seem that existing macro cannot find my home network Excel 1997 file)


    Take note those file with error will not be copied and you can see whether there is error or not in "Note". For example if let say one of the file you need to copy does not have "DATA" sheets, it will be skipped and error number 91 and status "Error Found" will be mentioned.


    Do try the new macro and if anything further do let me know.


    Sorry that I still do not know what trigger those errors you mentioned above :) My gut told me it is something about "networking" & firewall :)


    Yura

  • Microsoft Excel cannot access the file, compile via VBA

    Re: Microsoft Excel cannot access the file, compile via VBA


    hi vapid,


    I have tested the macro in windows 7 + windows xp home network environment using the dummy data but things seem to be ok :)


    I have tested the macro by placing the attached "Master4.xls" with dummy empty directory with no further sub directory and macro seem to give blank Master.txt as normal :)


    Have a tried on the Master4.xls on your life data to see you can somehow see something :)


    One note to share is that macro will open "Read-Only" file but skip "Hidden" or at least the Excel 2003 is so :)


    I will try testing further.


    For other who are reading the forum below is what the macro is doing:


    i) macro set Excel location to where the Master4.xls is in
    ii) macro will check Master4.xls for any previously "uploaded" file from a list in Sheets("Note")
    iii) macro will get the directories of the directory where Master4.xls is in
    iv) macro will try to find subdirectories
    v) macro will try open and copy any *.xls or *xlsx file found
    v) macro will loop


    And make sure reference to "Microsoft Scripting Runtime" is available.
    In order to check you need to do the following:
    - open Excel
    - go to Visual Basic Editor (Alt + F11)
    - go to Tools menu (Alt T)
    - go to Reference submenu (Alt T)
    - scroll down in the "Reference - VBA Project" pop up window
    - make sure "Microsoft Scripting Runtime" is ticked
    - then click "Ok" and close Visual Basic Editor



    Yura