Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Thanked 0 Times in 0 Posts

    Trying to create a cashflow formula but need it to also take due date and todays date into account

    Hi All,

    I’m trying to create a formula that will take into account the below for cash flow:

    As it stands the due date is the 24/07/14 and the week commencing date is 22/07/14 so the amount sits in the correct bucket with this formula: =IF((AND($B5>=J$4,$B5<K$4)),$C5,0) the formula in the next box just increments =IF((AND($B5>=K$4,$B5<L$4)),$C5,0)

    What I would like to do is if also take into account the data in D1 being todays date, so firstly if the due date is in the past I want zero in the box it should have fallen into and then for it to use the date in D1 as the due date to put it into the correct bucket, i.e. M5 should have the value and not J5

    Please see attached the file I uploaded, thanks in advance all
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Dec 2009
    Thanked 0 Times in 0 Posts
    Try this:
    =IF($B5>$D$1,IF((AND($B5>=J$4,$B5<K$4)),$C5,0),IF( AND($D$1>=J$4,$D$1<K$4),$C5,0)) (formula in j5)
    Last edited by markwl; 2014-08-11 at 13:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts