+ Reply to Thread
Results 1 to 2 of 2

Help With Calculating Number of Previous Days

  1. #1
    Registered User
    Join Date
    06-27-2017
    Location
    South Carolina
    MS-Off Ver
    Excel 13
    Posts
    3

    Help With Calculating Number of Previous Days

    Hi, Thank you for clicking. This is my first post on this sub and I come with something that I for the life of me cannot figure out. I am working on a project where I need to calculate the amount of days impacted in prior months for backdated contracts. The accounting team doesn't need to know about contract adjustments in the current month, but they need to know how much money to adjust for the previous month after all contracts have been invoiced. To put it in perspective, I work for an equipment rental company, so if a contract is written on 6/1 but was dated for 5/28, the accounting department needs to know to add 3 days rent on the prior month. I need to write a formula which will capture the following in a cell:

    -If the current date is 6/1 and a contract was dated for 5/30, the cell needs to say "2" since 2 days were added to the previous month.

    -If a contract was written on 6/5 for a start date of 6/1, the cell should say "0" because the prior month was not impacted.

    -If a contract was written on 6/1 for 4/1, the cell should say "61" since May and April were impacted.
    Any help is greatly appreciated. I am a recent college graduate and will be using Excel a lot in my current career. I promise to come back and contribute as my Excel knowledge advances. Thank you all.

    -If a contract was written on 6/5 for 4/1, the cell should still say 61 since only 2 previous closed months were impacted.

    I have already tried the formula: =IF(MONTH(A5)>MONTH(B5),A5-B5,0). The problem is it doesn't account for my last example, where it should subtract the number of days in the current month (6/June). There is an attachment with what I need on here.

    Any help is greatly appreciated. I am a recent college graduate and will be using Excel a lot in my current career. I promise to come back and contribute as my Excel knowledge advances. Thank you all.
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Help With Calculating Number of Previous Days

    Try this in C2

    =IF(MONTH(A2)>MONTH(B2),EOMONTH(A2,-1)+1-B2,0)

    Copied down column C.

+ 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. DAYS function: correctly calculating the number of days
    By dadpad in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-25-2015, 09:18 PM
  2. Calculating number of days
    By excelquestionz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2015, 02:08 PM
  3. Calculating number of days, help.
    By DollyMixture in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2014, 11:01 AM
  4. [SOLVED] Number of days in previous month
    By Tortus in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-01-2014, 11:23 AM
  5. [SOLVED] Calculating the number of days
    By bremen22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-09-2013, 12:06 PM
  6. calculating the number of days
    By Deanomcbeano in forum Excel General
    Replies: 12
    Last Post: 10-02-2012, 08:22 AM
  7. [SOLVED] Calculating number of days
    By Carlos in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2005, 05:06 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