+ Reply to Thread
Results 1 to 4 of 4

Using EOMONTH in formula checking two dates against first and last days of current month

  1. 03-08-2018, 07:39 PM #1
    BlissC
    BlissC is offline
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    Using EOMONTH in formula checking two dates against first and last days of current month

    Hi,

    I have a fairly simple spreadsheet for tracking projects with basic details of the project and whether the project is currently active or has been active in the current month. I have columns with a project start date (column H) and end date (column I). I've sussed the formula for checking which projects are currently active, but I'm stumped by the one to test if a project is/has been active in the current month.

    I'm fairly new to Excel formulas and functions, but I've realised that probably the easiest way to do it is to use the EOMONTH function to find the first and last date of the current month, and that I need to identify projects not only starting or finishing in the current month, but that potentially start before the beginning of the current month or end after the end of the month. I also need to allow for a blank end date which would mean that the project's still ongoing.

    I'm trying to get the formula to return a "yes" if the start date is on or before the last day of the current month or the end date is on or after the first day of the current month, or if the end date is blank. This is the formula that I've come up with:

    Please Login or Register  to view this content.
    Excel doesn't like this formula though, and I can't figure out why. Can anyone spot what's wrong with the formula please?
    Reply With QuoteRegister To Reply
  2. 03-08-2018, 08:39 PM #2
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,914

    Re: Using EOMONTH in formula checking two dates against first and last days of current mon

    Try this...

    =IF(AND(H5<=EOMONTH(TODAY(),0),OR(I5>EOMONTH(TODAY(),-1),I5="")),"Yes","No")
    Reply With QuoteRegister To Reply
  3. 03-08-2018, 09:03 PM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Using EOMONTH in formula checking two dates against first and last days of current mon

    welcome to the forum. do upload an excel sample so that we do not have to manually key in your data to do a testing.

    input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand. give several egs such as
    current month to current month. Yes or No?
    current month to next month. Yes or No?
    current month to blank date. Yes or No?
    last month to current month. Yes or No?
    last month to next month. Yes or No?
    last month to blank date. Yes or No?
    next month to blank date. Yes or No?

    the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

    if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

    Phuocam has provided a solution so i will explain why did your formula go wrong. when you typed in formulas, you will notice an Excel tip come up. put your cursor on the first AND formula in the formula bar. you would see the arguments shown for the IF formula that goes like:
    IF(logical_test,[value_if_true],[value_if_false])
    the bold part indicates which argument are you at.

    the logical_test part is something that needs to usually turn out TRUE or FALSE. if you click on the excel tip for the logical_test, it selects this portion:
    AND(OR(H5<=EOMONTH(H5,0)))
    place your cursor on the OR and now you see Excel tip for the AND formula. AND formula shows you 2 arguments. 2nd is optional but usually needs to be placed because AND is to fulfill at least 2 conditions to be TRUE. yours only has 1 condition:
    OR(H5<=EOMONTH(H5,0))
    it will work but no point putting in the AND formula.

    similarly for OR formula. you usually need 2 or more conditions. yours only has one. your OR formula has to be something like this:
    OR(H5<=EOMONTH(H5,0),I5>=EOMONTH(I5,-1)+1)
    you can click inside the OR formula inside the formula bar and see that you can click on logical1 or logical2. when either condition is fulfilled, it will be TRUE.

    but 1 more thing that is wrong above is H5<=EOMONTH(H5,0). let's say H5 is 2-Feb. EOMONTH of H5 would be 28-Feb. that wouldn't be current month. you have to use TODAY()
    H5<=EOMONTH(TODAY(),0)

    the AND formula will come in handy to check if H5 is <=end of month of current month AND if I5 is > than starting date of current month OR I5 is blank

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  4. 03-09-2018, 06:01 AM #4
    BlissC
    BlissC is offline
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    Re: Using EOMONTH in formula checking two dates against first and last days of current mon

    Thank you both so much. I've been trying to figure this out for days and getting nowhere!

    Phuocam's solution works perfectly. Thank you also benishiryo for taking the time to explain how post better questions and upload a sample, and particularly for taking the time to explain why my formula wasn't working. I'm new to Excel's functions and formulas and I'm still at the stage where I'm often trying to adapt formulas I've found online. I really do appreciate you taking the time to explain where I went wrong. Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count the number of days in the year to date less the days in the current month
    By Excelski in forum Excel General
    Replies: 16
    Last Post: 08-22-2017, 11:20 AM
  2. [SOLVED] want to get the remaining days of the current month based on that respective month's one d
    By bala04msw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2017, 04:47 AM
  3. total of days within current month with dates from before the current month and year
    By ozinal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  4. [SOLVED] Formula to Return # of Working Days (Monday through Friday) in the Current Month?
    By EnigmaMatter in forum Excel General
    Replies: 3
    Last Post: 10-11-2014, 09:04 PM
  5. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  6. Date Formula for days of the month, new dates starting on the 2nd of the month.
    By Kenn Jerger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2013, 01:31 AM
  7. Formula for days lapsed in current month with other variables to consider.
    By jondon in forum Excel General
    Replies: 10
    Last Post: 01-04-2012, 10:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1