Formula for calculations using dates under certain conditions

[JennyG]

Hi
I'm trying to build a spreadsheet that will be able to calculate current stock levels by taking into account what the last action was (e.g. stocktake, purchase, order), etc but I don't know how to write formulas that will do what I need them to do.

Any data from before the stocktake doesn't need to be taken into account, but if we've done a stocktake and then there have been orders or sales since then, I need that to be picked up.

So, if the most recent action was a stocktake (and the date in E2 was the most recent date), then B4=E4 (data is listed below dates starting from cell 4).

But if we've done a stocktake and then an order in or sale out, it will need to be B4=E4+C4-D4

Is it possible to even do this??

 

[James006]

Hi,

With Excel you can certainly do it ...
First, list all possible events and build the respective individual formulas...
Then, you will design the user interface, probably with data validation...

HTH

 

[JennyG]

Hi,

With Excel you can certainly do it ...
First, list all possible events and build the respective individual formulas...
Then, you will design the user interface, probably with data validation...

HTH

Thanks. By "build the respective individual formulas", do you mean figure out all the possible "if date X is before date Y, then" stuff?

And how do you do those formulas?

Thanks

 

[James006]

Hi Jenny,

In Excel, dates are numbers ... so if you want to compare two dates located in cells A3 and A2, just go ahead with:

=If(A3>A2,"Later","Earlier")

HTH