1. W

    Calculating time differences

    I have a Start Time and Stop Time column in my Excel spreadsheet that is feeding the Power BI reports. Time is entered in military time, however I cannot make Power BI Calculate the difference properly when the Start Time is one evening and the Stop Time is the following days morning (i.e...
  2. N

    Formula for Calculating Percentage through the year

    Hi, I'm trying to come up with a formula that calculates how far we are through the year in percentage. Right now I have: =YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()) But I'm not sure it's working properly. Any ideas? Thanks, Mark
  3. C

    Lookup matching value in rows with duplicates See above link. I want to find the lowest value of DEF. I have tried multple versions of vlookup, index/match/min. I am not able to get it done properly. Needs to be a function as i am not proficient with VBA.
  4. N

    Formula not working properly

    Would you please provide some feedback on the following formula? It is supposed to be converting the data in cell CSV!AQ3 from feet and inches to decimal inches and doesn't seem to be doing it properly. On my spreadsheet it's showing the result is 4.375 when (I think) it should be 19.625. The...
  5. L

    Restrict access to worksheet by password

    Hi, I have two worksheets, named as A and B. I would like to assign two different password to A and B. And if the user input A's password, the worksheet A will be shown.. and same for worksheet B. Some cell values in B is referring to A, thus, I would like to make sure B also displays the value...
  6. L

    cleaning/formatting data

    Hi I want to get live data from a buoy which come in the form of an online text file (it can be seen by clicking on the link below). I want to import this data and end up having only the data separated properly into their respective columns etc. Is this a power query job or VBA job...
  7. I

    Index+match+small+if+countif formula

    Good day, I have been using a formula below to calculate SP id's and sale amounts. It worked fine but suddenly it does not pick up the next ID number. Apologies - I do not know how to paste the example properly. {=INDEX($H$20:$H$29,MATCH(SMALL(IF(COUNTIF($K$19:K19, $H$20:$H$29)=0...
  8. N

    Formula to verify properly formed email address with no additional characters or blanks

    Does anyone have a canned Excel formula that will check a cell to confirm it is a properly formed email address with no extra characters or blanks? In cell A1 might be: or any similar properly formed email address Formula in cell B1 would return FALSE In...
  9. A

    Sort column with '000 format

    Hopefully this one is super easy, I have a column of numbers starting at 0 and going to 200 but must be 3 digits long. I have added '00 and '0 to all numbers below 100 so they display as 001, 002 etc but now they do not sort properly. This is how they show up <tbody> 113 200 201 202...
  10. M

    Pivot Table not properly grouping data

    I have a structured table, and there are three different columns representing a monthly period in the format YYYYMM (201809). The first is system generated, the second column is a manually input override period, and the third is a formula that takes the latest of the two periods. When I click on...
  11. W

    Opening Text File and copying data to single Worksheet

    I receive Gate records twice daily in a file in my Outlook. I move them to a folder on a shared drive to combine by weeks. I am trying to create a VBA code that will open the Current Week folder and copy all .TEXT files that are in the folder. They are all formatted the same but a caveat to all...
  12. P

    VBA to update Pivot Tables

    I have the below code to update pivots tables. It runs properly by itself. The problem I am having is that it does not update when i attach it to a button with another macro. The button calls on the pivot table refresh macro and a macro that pulls in data. It properly pulls in data but does...
  13. H

    Convert times to 24 hour clock

    Hi I am having trouble converting times, i can get it to work so far but all times with a zero at the end show as 3 digit, is there a way to convert it properly please? thanks for any help D <colgroup><col style="mso-width-source:userset;mso-width-alt:8996;width:185pt" width="246">...
  14. B

    Splitting an address field into separate fields

    I following the following address field that I received from one of our field offices. They combined the address and CSZ into one field. However, I need to have them all in a separate field for my formulas to work properly. I can't use formulas because until now, all of the fields have been...
  15. E

    Trim formula query

    Hi there, I'm trying to split a list of alpha numeric location references as shown in cell A1 into two distinct references that I require, as shown in cells B1 & C1. Can anyone assist? Any help would be grateful and I can tell my team off for not collecting the data properly in the first...
  16. D

    2 vlookups in 1 formula

    Hi All, im trying to put 2 vlookups into 1 formula although I just cant get it to work properly. Can someone please help! vlookup 1 =VLOOKUP(C3,Sheet2!AD:AE,2,0) vlookup 2 =VLOOKUP(C4,Sheet2!B:I,8,0) appreciate your help Sam
  17. J

    If statement problem

    =IF(OR(A4="WL"),("0"),0) + IF(OR(A4="BL",A4="DL",A4="LL",A4="LGL",A4="NL",A4="MDL",A4="RL",A4="WGL",A4="XL", A4="A",A4="B",A4="CB",A4="D",A4="K",A4="V",A4="X", A4="H",A4="L",A4="LG",A4="M",A4="MD", A4="N",A4="Q",A4="R",A4="T",A4="W",A4="WG",A4="Y"),(A7*B7),0) In the above statement, only BL...
  18. C

    Resize & Reposition Comments

    Hello. I'm new to the forum and I am not well versed in VBA. As you are aware, comments frequently get moved and resized. With the help of a friend, I was able to get the following macro to properly resize and reposition all comments in each worksheet within my workbook. It worked great for...
  19. U

    VBA Code to set print margins not working, unsure why

    Hello, I have a line of code which is <code> ActiveSheet.PageSetup.PrintArea = " $A$14:$P$87,$A$88:$P$155,$A$156:$P$221, $Q$14:$AE$87,$Q$88:$AE$155,$Q$156:$AE$221, $AG$14:$AV$87,$AG$88:$AV$155,$AG$156:$AV$221" </code> The first 7 pages print properly, but the last two are wrong. After running...
  20. B

    i need help with formula

    i have 6 numbers in a cell there is a Qusetion mark symbol with my 6 numbers i want to remove this symbol from Huge Data i have tried fnd and Replace ,but its not worked properly it is changing all my values iam looking for formula to remove that symbol

Some videos you may like

This Week's Hot Topics