1. R

    Find the last populated cell in a date range

    In column A i have a whole year of dates. In column L i have number. How do i look for the last number entered in column L per each month. so in one cell i would put January and (38) should show and for December (17484) should show.
  2. B

    Conditional Format Current Month and Those Prior

    Greetings! First I would like to say thank you for taking the time to look at my issue and offer a solution, your help is greatly appreciated. I am looking for help in creating a formula or direction on how to highlight dates in a cell(s) using Conditional Formatting for which are in the...
  3. H

    ODBC - SQL Statement help

    Hi all, I am using a SQL statement via ODBC to query a CRM table. I want to group the results by number of appointments in a month by company. So my raw data example is Company Name Appointment Date Appointment Type Joe Bloggs 01/01/2018 Audit Joe Bloggs...
  4. P

    EOMONTH Based on Month Doesn't Consider some dates

    Dear, i have used this: =AVERAGEIFS($N$4:$N$981;$J$4:$J$981;">="&(5&$AG8)+0;$J$4:$J$981;"<="&EOMONTH(5&$AG8;0)) for getting average of specific column while beside contains specific month. N Contains Number J Contains Dates AG8 Contains May but this formula seems doesn't consider that dates...
  5. J

    end of the month formula with only year and month

    Hello, my cell have "201801 Sale" in A1. What is the formula that I can use in B1 so that I get the end of the month date? (i.e. 1/31/2018) Thank you
  6. A

    Need formula to get EOMONTH

    How can I change this formula to get the EOMONTH? =IF(AND([@[Discussion]]>=$AQ$3,[@[Discussion]]<=$AQ$4),$E4+45,[@[Discussion1]]-89) If I use this formula I don't get the proper EOMONTH, so for FEB, I get 3/3/2018. It works ok for JAN, but I need the proper EOMONTH for the entire column...
  7. baitmaster

    Application.EOMONTH giving wrong answer

    Edit: Application or Worksheetfunction, both giving same issue Getting strange results in VBA when trying to work with EOMONTH. Debugging in Immediate I've got: ?format(WorksheetFunction.EoMonth(clng(Date),0),"dd mmm yyyy") I'm expecting it to return 28 Feb 2018 but it's coming up with the 27th...
  8. J

    Date extraction

    If I have "LAX Marketing Jan 17 True Up", what is the formula that I can use to return the result as "Jan-17"? I currently have :"IFERROR(EOMONTH(DATEVALUE(SUBSTITUTE(MID(A1,MAX(IFERROR(FIND({"Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep ","Oct ","Nov ","Dec "},A1),0)),6),"...
  9. reneev

    EOMONTH Formula Changed due to New Year

    I have the following formula in a report which takes the hire date (3/15/16) and converts it to the end of the month of the current year (3/31/17) which is when their next annual appraisal would be due. Works great.... =EOMONTH(DATE(YEAR(TODAY()),MONTH($J4),DAY($J4)),0) HOWEVER, now that it's...
  10. J

    Rank Sales between Months

    Hi, I would like to rank sales $'s for a selected product, based on date ranges. Selections can be made in A1 - Product type (eg: car) A2 - Date from (EOMONTH dates eg: 31-Jan-17) A3 - Date to (EOMONTH dates eg: 30-Jun-17) In row 4 are headings, and row 5 ownards: Column A - EOMONTH dates...
  11. C

    Simplifying conditional format ranges which uses EOMONTH function

    I need to simplify the X and Y lines below. In the future I will need X to cover a 7 to 12 months away and Y to cover 12 to 24 months after that. $A$1 = today() The below formula looks at B4 end of 0(same) month and asks if $A$1+1 month is the same, or if $A$1+2 is the same, or if $A$1+3...
  12. thorpyuk

    Does EOMONTH work with array formule?

    Hi All, I have a formula that works perfectly well, but it's a little sluggish: ={SUM(IF(MONTH('Entries'!$C$2:$C$50000)=MONTH(L$34),IF(YEAR('Entries'!$C$2:$C$50000)=YEAR(L$34),'Entries'!$H$2:$H$50000,0),0))} I thought i might use EOMONTH to speed up rather than evaluating the date twice...
  13. J

    Formula that would roll forward the amount (netting the amounts)

    So, with a help from great excel experts in this forum, I was able to get the formula that would give me the results for STEP 1. Now, I need help for STEP 2. F or an example, I want ABC's Session 2 for Feb-17 to be the net value of from both Jan-17 and Feb-17, which would equal to ZERO. Then...
  14. N

    Same formula - different result in other workbook

    I have created formula to determine if a date falls is between two dates. The formula looks like this (in a Table): =AND([@CalcDatesMerged]>EOMONTH(TODAY();-14);[@CalcDatesMerged]<=EOMONTH(TODAY();-1)) I tested the formula in a dummy workbook and the result was like I expected it to be...
  15. N

    Can i change the time stamp on EOMonth Formula

    Hello, I am hoping this is a really simple one. I have tried searching the world wide web for the answer, however, I am a bit poorly today and not functioning properly. I have the following formula; =WORKDAY(EOMONTH($D$1, -1),1) Which gives the date that a report is due by. I am then...
  16. D

    Value and date range formula (Expert)

    Hi I am looking help for a formula concerning values and dates in a range. Table 1 consist of aggregate audit values at specific dates. These audit values represent the sum of values between the dates. Table 1 <tbody> Audit values 3.1.2017 5.1.2017 9.1.2017 Value 12 10 24 </tbody>...
  17. T

    Last friday of month excluding holidays

    Hi, I have a formula that gives me the last Friday of the month, but would like to ensure that holidays are considered in the formula. I have a list of holidays in a table named 'Holidays'. I have tried to add this in to the formula, but have had no luck...
  18. R

    EOMONTH/WORKDAY question

    Hi guys, In cell A1 I need to get todays date i.e =today() or 5 Sept 2015 - that's what I can do.. In cell A2 I need to see the last working day of the previous month i.e. 31 Aug, in cell A3 I need the previous last working day i.e. 31 July. Ive used EOMONTH but I struggle with the cell A2...
  19. N


    Hey, I would like to count all employed people(Employed) who graduated from uni (G) and received professional qualification (Q) during the month of May. So far this formula works well; =COUNTIFS('Sheet 2'!B:B,"Employed",'Sheet 2'!R:R,"G",'Sheet 2'!AB:AB,"Q",'Sheet 2'!Z:Z,">4/30/2015") However...
  20. D

    Find last Friday before last full week of the month

    Hi, I would need a formula that would find the last Friday before the last full week of a month (week with holidays are not considered full week). For example, in 2014, the formula would give the following dates for each month: Jan 24, 2014 Feb 21, 2014 Mar 21, 2014 Apr 18, 2014 May 23, 2014...

Some videos you may like

This Week's Hot Topics