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...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back