+ Reply to Thread
Results 1 to 2 of 2

Need to classify expenses based on when were incurred

  1. #1
    Registered User
    Join Date
    12-17-2018
    Location
    United States of America
    MS-Off Ver
    Excel 2016
    Posts
    1

    Lightbulb Need to classify expenses based on when were incurred

    Please some help here:

    This is how my table looks like. I would like to classify each amount into the corresponding week using starting date of 01/15/2019. I will use this template for different projects so starting date will vary from one project to another.

    Date Amount Week 1 Week 2 Week 3 Total
    2/3/2019 200
    2/5/2019 250
    2/9/2019 350
    2/11/2019 258
    2/18/2019 900
    3/5/2019 280


    Total $- $- $- $- $- $- $-

    So if I incurred in expenses 7 days after my starting date (01/15/2019 in this case) I would like those 200 to appear in the Week 3 (01/15/2019 +7 days would be first column, 01/15/2019 +14 days would be in week 2 column, 01/15/2019 +21 days would be Week 3 column and so on).
    Not sure if Im explaining my self clearly, but I would like to know a total spent by week based on the starting date and when the expense was incurred and bring that amount to the correct week in order to totalize it by week.

    Thanks!!!!

  2. #2
    Registered User
    Join Date
    12-17-2018
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Need to classify expenses based on when were incurred

    Reference date = 01/05/2019 - you could have this as a named range, a today() formaula, or a cell eg. 1A

    Formula for:
    Week 1 =IF((reference date - row date)<=7,row amount,0)
    Week 2 =IF((reference date - row date)<=14,row amount-Week1,0)
    Week 3 =IF((reference date - row date)<=21,row amount-Week1-Week2,0)

+ 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. Classify a data set by A-B-C based on % of total values
    By greatgenes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2017, 02:10 AM
  2. [SOLVED] Distribute Expenses based on specified rules
    By drgkt in forum Excel General
    Replies: 1
    Last Post: 02-10-2017, 10:28 PM
  3. Formula to compute hours incurred in a day
    By puuts in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 07:08 AM
  4. Classify based on user-defined categories
    By d_abbatelli in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2014, 11:07 AM
  5. [SOLVED] Help with Formula to Classify Shipments based on differnt materials on an order
    By Pi* in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-06-2013, 09:59 AM
  6. Copy expenses based on dates to different banks.
    By strecjt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2011, 09:01 PM
  7. Summing expenses based on month & Vlookup
    By antonc in forum Excel General
    Replies: 8
    Last Post: 06-05-2008, 03:41 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1