order checking/verifying cells in excel

[NitroNB]

is there a way to check seperate columns in excel , date ordered, date required, date arrived - against each other and flag up when a date required exceeds date arrived or perhaps a couple of weeks beforehand.

there may be lots of rows so i need an efficient way of checking.

 

[xlbo]

say date required in col F and date arrived in col G then
in a new column:
=if(G2-F2>0,"Arrived Late","OK&quot

should get you started

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[NitroNB]

what i'm trying to do is flag up in a colunm when an ordered item is late, i.e. when the delvery date has gone beyoned the expected delivery date and also to try and flag up an order a short time it is due so as to see when its expected.

 

[xlbo]

Isn't that what I gave you ???
If you want to check to see all those that have been delivered after the expected date, the formula I gave you will do that. If you want to check against Today's date, just substitute TODAY() for G2
If you want to see a week in advance, just use TODAY()+7

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[NitroNB]

yeah thanks,

I was trying to do it in a vb macro as i've only used vb before and not excel/spreadsheet.

 

[NitroNB]

ok got it working now.

next step is how can i make the number of days a variable so the user can enter how long before to flag up, like perhaps an input box to pop up when user is about to enter a flag date.

=IF(TODAY()+7-E3>0,"Late","OK&quot

where 7 is days before item is due, lets say becomes a variable,
integer x,
the user then enters a number of days that is the lead time of the flag so to speak.

is it possibel to create a function excel that can do this or would vb need to be used?

is it too complicated to use vb in excel for this, as then i could use data verification and write data to a text file so that other programs can use it.

cheers

 

[xlbo]

No - it would be pretty easy to use vb but.....why bother - all that you are asking can be done in formulae which is completely native to excel - why add the extra overhead of supporting some code ??
=IF(TODAY()+7-E3>0,"Late","OK&quot
Just select a cell to use as your "Lead Time Variable" and label it so the users can see it - lets say H3
Then, your formula would be:
=IF(TODAY()+H3-E3>0,"Late","OK&quot


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244