Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    52
    Thanks
    6
    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. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Minnesota
    Posts
    1
    Thanks
    0
    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 14: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
  •