1. M

    Conditional format with EOMONTH function

    Hi guys, I have tried to add conditional format with EOMONTH function. But I'm not sure what I do wrong, they make the condition that I made but not on the last day of month. In the below picture: The condition should appear on 30Apr and last day of the other month that what I expect.
  2. AWM21

    Is there a better replacement for this formula?

    Hello, I'm betting there is a better way to run this formula that references a block of dates by month, and returns the table rows based on the short date related to the selected month. In the example below, I have a dynamic array showcasing the rows of data from a table based on the month...
  3. G

    reference cell even the row is deleted

    i have no clue to doing this man, i expect the output is to be the right chart, but on the left chart is what i’ve done i want the cell to still remain the same number even the row is deleted and if i add new no. it still going +1 i need your help guys
  4. 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.
  5. 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...
  6. 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...
  7. 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...
  8. 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
  9. 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...
  10. 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...
  11. 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),"...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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>...
  20. 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...

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