 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Ok. Say i have a list of transactions:
Account Due Date Amount
ABC001 13/08/2012 136.55
ABC002 15/08/2012 42.6
ABC003 21/08/2012 551.11
...
Bugger, hold on!
Holding.
Ok, so I have that list of sales transactions with payment due dates above, and want to forecast my money in over the next say 4 months as well as identify everything overdue.
I first thought I'd be smart define a [forecast month] (eg 9) and do a sumif < [forecast month] to find over due, then sumif [forecast month] +1 etc for the future months.
I then realised that won't work when I get close to month 12 and it starts expecting month 13 etc.
So I need to work with proper dates, I remember dateserial in Access from years ago but can't find something similar in Excel.
Anyone got any smart ideas?
Either create a lookup table with the date and period or just use a forecast date? You can display the month while retaining the underlying date value in a cell if you use a custom number format.
Excel stores dates/times as a number. Each increment represents one day (hours, minutes, seconds, etc. can be represented as fractions of one day).
You can therefore express the difference between two dates as a simple number. So to check if a date is less than 9 months ahead (assuming 30 days per month), you would use an expression such as:
sumif(A1<(TODAY()+270))
(Probably some horrible syntax there, I haven't checked my formula).
Alternatively, you can separate out the months - e.g.:
sumif(month(a1)<month(today()+9)
Again, sort the syntax yourself! I think Excel is smart enough to handle the month resetting after December.