+ Reply to Thread
Results 1 to 7 of 7

End dynamic EOMONTH calculation at the end of the month

  1. #1
    Registered User
    Join Date
    05-03-2017
    Location
    goleta, ca
    MS-Off Ver
    2010
    Posts
    3

    End dynamic EOMONTH calculation at the end of the month

    Hi Everyone,

    Excel noob here. I'll try to explain this as best I can.

    I've created a formula that allows me to predict gross revenue in a given month based on where I am at any particular day in the month.

    For example, if I'm on day 10 of a 30 day month and I've generated $1000 in revenue so far in the month the formula will tell me that I'll be at $3000 on day 30.

    The problem is that once the month is over the formula resets and keeps going, skewing the data. I'm hoping to update the formula so that it knows to stop running after the last day of the month and stays at the final number.

    Here's my current formula: =SUM(N5)/day(TODAY())*DAY(EOMONTH(K1,0))

    N5 = total revenue in the month so far
    K1 = Month (in this case April 2017)

    Please let me know if this makes sense or if further info would be helpful. Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: End dynamic EOMONTH calculation at the end of the month

    Can you please upload a sample workbook with the expected results (enter manually)?

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: End dynamic EOMONTH calculation at the end of the month

    rabiancone welcome to the forum.

    Yes an uploaded workbook would be good. You usually get more and faster response when you do.

    If you are not familiar with how to do this:

    To attach a file to your post,
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    • be sure to desensitize the data
    The file name will appear at the bottom of your reply.
    Dave

  4. #4
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: End dynamic EOMONTH calculation at the end of the month

    Hi
    Instead of entering month in K1, enter date against current total.
    and change the formula to =SUM(N5)/DAY(TODAY())*DAY(EOMONTH($K5,0))

    K5, K6 , K7...... will be the dates..

    a dummy file also attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-03-2017
    Location
    goleta, ca
    MS-Off Ver
    2010
    Posts
    3

    Re: End dynamic EOMONTH calculation at the end of the month

    Hi Everyone,

    Thanks for your help so far! Sadath - I appreciate your suggestion but unfortunately is not a solution for my specific use case.

    I've attached a sample copy of the workbook for reference.

    In the sample you will see fields for April 2017 and May 2017.

    In this example May is calculating correct as it is in the current month, however April is incorrect as it continues to re-calculate although the month is over.

    My goal is that it stops calculating at the end of the month and ends with the final total, in which case would be $8500 for the month of April, which was total accrued revenue by the end of April.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: End dynamic EOMONTH calculation at the end of the month

    Hi
    pls change the formula to.

    =IF(EOMONTH(TODAY(),-1)>=K5,N5,SUM(N5)/DAY(TODAY())*DAY(EOMONTH(K5,0)))

  7. #7
    Registered User
    Join Date
    05-03-2017
    Location
    goleta, ca
    MS-Off Ver
    2010
    Posts
    3

    Re: End dynamic EOMONTH calculation at the end of the month

    Sadath,

    Thank you, that works! I'm glad I found this board, otherwise I would have spent hours trying to figure it out on my own and wouldn't have come close, lol.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adjust EOMONTH formula to return closest end of month date to today?
    By nobodyukno in forum Excel General
    Replies: 8
    Last Post: 01-19-2017, 07:51 PM
  2. [SOLVED] EOMONTH + WORKDAY for more than 1 month!
    By Keibri in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2016, 06:12 AM
  3. Using EOMONTH to stop a formula on the 1st Day of the next month.
    By RYLEE.HART in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-31-2016, 05:02 AM
  4. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  5. [SOLVED] Date functions YEAR, MONTH, EOMONTH do not work for the array format?
    By alice2011 in forum Excel General
    Replies: 2
    Last Post: 05-06-2015, 08:59 PM
  6. [SOLVED] EOMONTH formula calculation only work days
    By kettlecorn22 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-03-2014, 03:26 PM
  7. Rolling 12 Month Dynamic Calculation
    By bapswarrior in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-29-2013, 04:37 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1