hello, i have a date variable, and i would like with VBA to get the latest day from the previous month many thanks Function LastPrevDate(Dt As Date) As Date LastPrevDate = DateSerial(Year(Dt), Month(Dt), 0) End Function Sub test() MsgBox LastPrevDate(Now) End Sub HTH. Best wishes Harald "Laurent M" <anonymous@discussions.microsoft.com> skrev i melding news:06a701c503b0$3a4b7bc0$a401280a@phx.gbl... > hello, > > i have a date variable, and i would like with VBA to get > the latest day from the previous month > > many thanks In case you specified VBA be...

The following is an array formula.... =IF(OR(G26="",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"",MAX(IF($G$16:G26=G26,$H $16:H26)+1)) 1. Is there a way to enter this into the cell (G26) using VBA (as an array)? 2. If so, if the row that this is to be entered is diff to row 26, how does this get entered into say, G35 and the references changed appropriately? Rob Hi Rob, try this Sub rob() Range("F26").FormulaArray = "=IF(OR(G26="""",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G26=G 26,$H$16:H26)+1))" ...

hi what formula should i use if i want to work out the date of say the 15/6/04 plus 5 months from now?? thanks : -- Message posted from http://www.ExcelForum.com the only way i know is to type this formula into the cell: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) the cell "A1" (or any cell) will be the cell that the original date will be in. just add in the 6 after the MONTH (A1) to get your new date hope that helps >-----Original Message----- >hi what formula should i use if i want to work out the date of say > >the 15/6/04 plus 5 months from now?? ...

Hello Excel community friends. Here's my problem: I trying to copy a formula from a worksheet in one workbook to a worksheet (of the same name and same cell location) in another workbook. Say the formula from workbook1/worksheet1 is =sum(1+1) and the name of workbook1 is "Me" and the name of the worksheet1 is "Sheet1" and located in cell A1. Of course, when you copy the formula from "Me" to another workbook (named, say, "You") and to the same location (A1) and on the same named worksheet, "Sheet1", the preface of the formula is s...

Any suggestions on how to use this formula in Access? if not(IsNull({MASTER.JMT_DATE})) then "Post Judgment"else if (IsNull({MASTER.JMT_DATE})) and not(IsNull({MASTER.SUIT_DATE})) then "Post Suit, Pre Judgment"else if (IsNull({MASTER.SUIT_DATE})) and (IsNull({MASTER.JMT_DATE})) then "Pre Suit" Here is a guess -- IIF([MASTER].[JMT_DATE] Is Not Null, "Post Judgment", IIF([MASTER].[JMT_DATE] Is Null AND [MASTER].[SUIT_DATE] Is Not Null, "Post Suit, Pre Judgment", IIF([MASTER.SUIT_DATE] Is Null AND [MASTER].[JMT_DATE] Is Null, "...

Hi Firstly thanks for many tips picked up in this ng - I've learnt a lot. Copying and adjusting formulas 26 sheet workbook (Excel 2000) with a sheet for each weeks figures broken down departmentally and totalled for the week. From sheet 2 onwards the sheet has year to date being current weeks figures + previous year to date. This was originally in .123 and as you copied from one sheet to other sheets the formula adjusted the sheet name - in Excel it keeps the formula without adjustment. I find this weird (and very long winded as each sheets formula will need changing). Am I missin...

Hello I'm trying to add a number that was calculated by means of a formula in one column to the sum of a different column. Example: Column A (running balance) Column B (sum) =+e5+c6-d6 =sum(h1:h8) Col A's number is running balance. I copied the formula down so that the next row changes the formula to =+e6=c7-d7 I want that running balance to add to a sum in another column. I can add the first cell to it, but it doesn't change if the running balance changes. I know that is because it's pointing to that single...

Hi, Does anyone know how I can get the number of years and months between 2 dates? (Using Office 2000) Thanx Subtract one from the other "Els" <Els@discussions.microsoft.com> wrote in message news:E54C0D76-1313-40EF-922C-FCB0780AD992@microsoft.com... : Hi, : : Does anyone know how I can get the number of years and months between 2 : dates? (Using Office 2000) : : Thanx Hi see http://www.cpearson.com/excel/datedif.htm >-----Original Message----- >Hi, > >Does anyone know how I can get the number of years and months between 2 >dates? (Using Office 200...

I'm not familiar with the proper terms, but I am trying to collect information in a schedule I have created. Is there a way to have three different totals for count only from the same three vertical columns. I will be glad to explain further, if someone can help. I may not be asking in the proper terms. Thanks for any help. Give more specifics, like column letters and row numbers and what you want to count. ************ Anne Troy www.OfficeArticles.com "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:00BDEBCD-CA3E-49E0-B0EC-328A1E4EB74E@microsoft...

I've put formulas in eacg column C cell to get me the percentage of th column B total in each cell of column B. Formula is B1=sum(B1:B10) and this works but I can't sort and retain the proper percentage. Ther must be a way to do this that would allow me to sort and maintain th proper value. Any help -- Message posted from http://www.ExcelForum.com You need to make the reference to B1:B10 absolute. Use: =B1/SUM($B$1:$B$10) HTH Jason Atlanta, GA >-----Original Message----- >I've put formulas in eacg column C cell to get me the percentage of the >column B total in each...

Created a calendar page for the office with custom logo and address info. The calendar wizard changes everything back to the default when changing the date range. Help file says to add dates to retain custom elements but not 'How' to add dates. What's the best way to create a custom calendar and retain the formatting? The address lines and the logo are determined by your personal information. Look under Edit, the personal information is the very last item in the menu. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com ...

Hi, I hope you can help me with this formula. I have two spreadsheets called the following:- 1) SME main 200410 2) SME PRE accounts CAT1_RCB checked 120310 (2) Coloumn A in both spreadsheets contains a list of numbers (e.g. 1012381211420) and both coloumns contain 6905 rows. I need a formula in cell b1 of spreadsheet 2 that will look at the number in cell a1 of spreadsheet 2 and if that number is in coloumn A of spreadsheet 1 then I want cell b1 of spreadsheet 2 to return a value of Yes if not then I want a value of No Thanls It's easier to set this up with bot...

I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

Hi All, Is there any way (I hope so) to set the maximal size of the formula bar? I have large formulas and the expanding formula bar is simply hiding half of my sheet and a lot of cells I need to see. Please advise (no, writing smaller formulas is not an option :) Thanks Marton No, it is no adjustable AFAIK. Why is not writing smaller formulae an option, you can put intermediary steps in hidden columns, it also makes debugging easier. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "VilMarci" <dontsend@here.mail> wrote in mes...

If I want a column to look at a column containing a date, and figure th persons age at that time, I am using this formula.: =DATEDIF(H371,A371,"y") However, I would like to modify this so that if no date is given, i column H, it will return "Undisclosed", and if no data has been entere in either column a or h, it shows blank. I know that this is doin wit ,"" but I am not sure where to put it? Can anyone help -- Message posted from http://www.ExcelForum.com Hi try =IF(H371="","Undisclosed",IF(A371="","",DATEDIF(H371,A37...

I got 3 columns in excel. First two is ID & Name, third column is a subset of the 2nd Column. Now, I want the corresponding ID's for my 3rd column (which should be looked up in first 2 columns) in my 4th column. ID,Name,Subset-name,Subset-ID? -- Failing to plan is planning to fail On Sun, 25 Apr 2010 01:24:04 -0700, Joe <Joe@discussions.microsoft.com> wrote: >I got 3 columns in excel. First two is ID & Name, third column is a subset of >the 2nd Column. Now, I want the corresponding ID's for my 3rd column (which >should be looked up in fir...

I want to set up a macro that copies a range to a new range, copying formulae and cell formats, but ignoring raw data existing in the range copied from. In other words I'm expanding a blank copy for a new period. One way would be to name a blank copy in a range elsewhere and copy it in at the cursor, but for various reasons I'd prefer not to do it that way this time. I have made a 2 stage copy procedure, using the "Paste Special" Alt ESF and Alt EST routines, but this process also copies raw data, presumably seeing the data as a label, even although I have formatted the...

I have a userform that contains windows media player. I want to set CommandButton4 to take the value of the formula in cell A2 of the active sheet and set it as the value of variable named 'inset'. I'm finding the VBA code on this much harder to write than I thought it would be. Any ideas please, anyone? Hi Teepee I am a bit confused by your request but I think that it as simple as: Private Sub CommandButton4_Click() inset = Range("A2").Value MsgBox ("The value of cell A2 is: " & inset) End Sub Regards Shau -- Shaun -----------------------------...

How can I increment and decrement column characters/letters using worksheet functions? I have a list of 5 characters corresponding to columns. The first character might be A, for column 1. How can I get the second character in the list to automatically configure itself to be B, the third C, the fourth D and the fifth E? That is, I want to set this up so that the second character is linked to the first, the third to the second and so on. That way if I change the first character from A to D then the second character in the list will automatically become E, the third will change to F, the f...

I have a summary file that has 15 links to 7 files in a single cell. When all the files that are being linked to are open, there is no problem. When just the summary file is open however, the formula box displays the entire path and filename which gets quite lengthy. In Excel 1997, the formula box cuts off after 1,054 characters I believe, so some of the links get cut off in the formula box. However it does appear Excel maintains the links as long as no changes are made. (Changes can be made safely with all linked files open) The end result is I'm not sure how much faith I can have tha...

Is there anyway to take an equation written in Equation editor and convert it to an Excel formula? You'll have to do that manually. And it is very interesting work. :) "DW in SF" <DW in SF@discussions.microsoft.com> wrote in message news:2FC66A71-338C-40CC-9B9C-32176388F3AF@microsoft.com... > Is there anyway to take an equation written in Equation editor and convert > it > to an Excel formula? I just Used the Equation Editor to record a Chemical Equation so I doubt that the editor can do this. However there is a "MATHFORMAT" that might do some ...

I have seen this done, but I do not know how. A formula was exploded to see it very clearly, a bit like a comment. How do you do this? <Ctrl><`>. The ` key should be to the immediate left of the 1 on your keyboards (US). HTH Jason Atlanta, GA >-----Original Message----- >I have seen this done, but I do not know how. A formula was exploded to see >it very clearly, a bit like a comment. How do you do this? >. > > ...it very clearly, a bit like a comment. Just a guess here. Try Tools | Options | Edit Tab, and select "Edit directly in cell." You...

I am taking a chem lab and i have to add the y=mx+b formula to my graph. I know that I should be able to do this but i do not remember how. Plot some points. Select the series, and on the Chart menu, select Add Trendline. Add a linear trendline, and on the Options tab, select Display Equation on Chart, and if desired, Display R-Squared Value on Chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jessrand" <jessrand@discussions.microsoft.com> wrote in message news:BE3D8678-24A2-4D76-8006-AD7F3E59EB21@mic...

Please advise me on how to write a formula that will start with January 2003, when I copy it to the next row it will increase the month to February and so on. When I get to December 2003, the following month will be January 2004. Thank You Hi msw! A1: 1-Jan-2003 A2: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Copy Down Select the range of cells Format > Cells >Custom format Type in the text box mmmm yyyy OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. ...

I am trying to use the data from one cell as part of a formula i another. For example: I have a three sheet workbook. The first sheet contains lists of data to be used in the followin sheets. i.e. Column 1 is List 1, Column 2 is List 2 etc The rows contain values. i.e. Row I contains Value 1, Row 2 contains Value 2 etc. The following sheets, named ListData1, ListData2 etc must contain dat from these lists laid out differently. I can achieve this by entering the formulae to refer back to Sheet1. e.g. For ListData1 ... =Sheet1!A1, =Sheet1!A2 etc For ListData2 ... =Sheet1!B1, =Sheet1!B2 etc F...