+ Reply to Thread
Results 1 to 15 of 15

Calculating values between dates

  1. #1
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Calculating values between dates

    Please see attachment.

    Thank you for your help!

    S
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Calculating values between dates

    Try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    Thank you for your prompt response.

    That formula does not allow me to find the true expense when the dates I set do not correspond with the billing period.

    I hope what I am saying makes sense.

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    I think I interpreted things a little differently than AlKey? I went with the following:

    =(INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1)+1)-$B$7)*INDEX($D$2:$D$4,MATCH($B$7,$A$2:$A$4,1)+1)+($B$9-INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)

    Which returns a value of $137.41. My assumption is that you were looking to combine the pro-rated portions of the two months overlapped by the chosen dates. Fair warning, though, my formula won't work if the dates stretch over 3 pay periods.

  5. #5
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    Cantosh, your interpretation was correct.

    However, the formula doesn't work also when the dates I set belong to one pay period only.

    Thanks for your help though.

    S

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Calculating values between dates

    Quote Originally Posted by simonplus View Post
    Please see attachment.

    Thank you for your help!

    S
    You gave us no explanation of what you need. I don't see how you can expect to receive a satisfactory answer to your problem.

  7. #7
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    Quote Originally Posted by simonplus View Post
    the formula doesn't work also when the dates I set belong to one pay period only.
    Good point. Try this one:

    =($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)+((($B$9-$B$7)-($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)))

  8. #8
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    Alkey: I know, and I am sorry for that. But, I am at work and didn't have the time to thoroughly explain the problem I am trying to solve.

    Cantosh: Calculations are still off by few dollars. Also when the dates correspond to the range limits or to the upper one only, the formula returns an error.

    Feel like we

    Thank you all for your help.

    S

  9. #9
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    I suspect the issue might be that it isn't clear which pay period your limit dates correspond to. Should 5/2 be attributed $3.40 or $3.48 for the day?

    Similarly, if B9 is 5/25, should the total amount include a charge for 5/25, or just the days up until 5/25?

  10. #10
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    The period ought to end the day before the following period begins

    The period beginning on 4/3 should end on 5/1 - charge $3.48 on 5/1

    Hope this helps

  11. #11
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    Thanks for the clarification. I think some of the miscalculations were due to the fact my earlier formula wasn't correctly accounting for the last day correctly. It was also struggling with ranges contained within one pay period, clearly.

    Try the new formula below. I know that it can be shorter, but I had one working formula for ranges within a period, and one working formula for ranges across two periods, so I ultimately just combined the two with an IF clause. If you get miscalculations, please let me know specifically what they are (e.g. "For dates X and Y, I should get $123.45, instead I'm getting $126.85). The formula does not return a value if B9=6/1 because the daily amount for 6/1 would go in D5, which isn't included in your sample.

    =IF(INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))=INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1)),($B$9+1-INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)-((($B$7-INDEX($A$2:$A$4,MATCH($B$7,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$7,$A$2:$A$4,1)+1)),($B$9+1 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1)))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1)+1)+((($B$9-$B$7)-($B$9 - INDEX($A$2:$A$4,MATCH($B$9,$A$2:$A$4,1))))*INDEX($D$2:$D$4,MATCH($B$9,$A$2:$A$4,1))))

  12. #12
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    Well done!

    I think it works.

    What was throwing me off was the number of days calculated by the datedif formula. The formula isn't counting the day corresponding to one of the range limits.

    :D

  13. #13
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    Yeah, that made things challenging from both an intuitive and formulaic standpoint. If it's within your power, it might make sense to add a column so that you can have: Start Date, End Date, Days, Charge, Avg/Day. Then again... if you've got something that works, sometimes it's best not to meddle. For now, I'm just relieved we got something to work!

  14. #14
    Registered User
    Join Date
    01-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    77

    Re: Calculating values between dates

    Question: what's the function of that +1 in the formula? Does it have to do with including the range limits?

  15. #15
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Calculating values between dates

    Correct. The +1 includes the end date listed in B9; it treats that date as 'Through B9' rather than 'Until B9'. To Excel, 5/8/2015 - 5/7/15 = 1, when we need that to actually count as two days. I believe I overlooked that contingency in at least one of my failed efforts above.

+ 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] Calculating weekly average of values from different dates in a month
    By Nitinkumar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2015, 06:14 AM
  2. [SOLVED] Calculating two values between two dates (per week)
    By kettlecorn22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2014, 01:47 PM
  3. calculating values over dates
    By neocuproine in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-15-2012, 03:10 PM
  4. [SOLVED] Excel calculating same dates as different values???
    By hijinxx in forum Excel General
    Replies: 4
    Last Post: 05-15-2012, 06:10 AM
  5. Calculating Values based on dates and indexes!
    By Nienaber in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2010, 04:58 PM
  6. [SOLVED] Calculating Dates Using Different Values for NETWORKDAYS
    By Kelly in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-22-2006, 10:15 AM
  7. Calculating for number of days when values are in dates
    By pumper in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2005, 01:08 AM

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