+ Reply to Thread
Results 1 to 7 of 7

Incremental increase in date in formula

  1. #1
    Registered User
    Join Date
    06-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    Incremental increase in date in formula

    Apologies if the thread title is not clear. I currently run a spreadsheet to capture dividend payments from shares month on month within a given year. The data is pulled from another tab called "Dividend Income". The formula I use is (example below is for November 2019):

    =SUMIFS('Dividend Income'!$D:$D,'Dividend Income'!$A:$A,">="&DATE(2019,11,1),'Dividend Income'!$A:$A,"<"&DATE(2019,12,1))

    This takes all those payments marked as November 2019 and sums them together to give a total. Every year I have to manually edit the formula, changing the year within the brackets, so for 2020, I would manually edit this formula to read:

    =SUMIFS('Dividend Income'!$D:$D,'Dividend Income'!$A:$A,">="&DATE(2020,11,1),'Dividend Income'!$A:$A,"<"&DATE(2020,12,1)).

    Simply dragging the formula across to another cell does not automatically update the year. My question is, is there a way that I can edit this formula so that dragging it across to another cell automatically updates the year?

    Table is below, with cells K3:K14 the ones to update.

    Screenshot 2019-12-16 at 12.14.49.png

    Max

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Incremental increase in date in formula

    You could use formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in C3, then copy across and down.

    Better still, you could use a pivot table...
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    06-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Incremental increase in date in formula

    Thank you Olly. Works perfectly.

    I did try to create a pivot table for the data, but wasn't having much luck.

    Max

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Incremental increase in date in formula

    If you post a sheet as suggested in the yellow banner we might give a try at a PT..

  5. #5
    Registered User
    Join Date
    06-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Incremental increase in date in formula

    Great thank you. I've uploaded the attachment with representative data.

    Max
    Attached Files Attached Files

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Incremental increase in date in formula

    Insert a pivot table based on your table of data.

    Add [Date] the the Rows section. Excel may automatically group by Years / Quarters / Months. If not, then right click on date field, and choose Group... then select Years and Months.

    Leave field [Date] in Rows. Move field [Years] to Columns. Remove [Quarters] if required.

    Add field [Credit (£)] to Values. Choose to Summarize Values By Sum.

    Format to suit.
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Incremental increase in date in formula

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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] Incremental increase formula
    By allgeef in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-09-2018, 10:57 PM
  2. [SOLVED] Formula for incremental increase of count
    By BRISBANEBOB in forum Excel General
    Replies: 3
    Last Post: 09-15-2016, 09:21 AM
  3. Replies: 2
    Last Post: 05-19-2016, 03:49 PM
  4. Incremental increase by week when the total for month is known
    By Redsohare in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-09-2014, 09:38 PM
  5. Need to calculate incremental increase
    By denk15 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-25-2013, 08:03 AM
  6. [SOLVED] Incremental Date Increase in Body of Text Using Word
    By golfervaughan in forum Word Formatting & General
    Replies: 6
    Last Post: 07-17-2012, 10:27 AM
  7. Formula for incremental increase
    By alexrm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2005, 06:05 PM

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