Log In
Register
Home
New Member
January 4, 2019
Offline
HI ALL,
Pls view attachement, copied in word. This is regarding excel formula. 'F3' is result of TODAY()-E3 (Date converted in numbers), so its showing due by number of dates by subtracting PO DUE DATE from TODAY(). But when G3 (Material received) is there, F3 still shows delay while there is no delay as material has already received.
If I am putting this formula in place of f3: "=IF(OR(G3>0, F3=0), TODAY()-E3, 0)", then its shows '0' which I required but same formula can not be use at places where material still NO RECEIVED and required to put DELAY in numbers 1,2 3,4,5,...etc. and not 0.
Please help
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Welcome to MOTH.
It is always better to upload Excel files than Word documents if you have some issues with Excel formulas. But let's give it a try.
First some questions.
1) Do you still want to see number of days due if received date is after due date?
2) Are the cells in G column empty or do you have a zero (0) when no receipt has been made?
3) Are the dates in D, E and G columns in date format?
So, without having the answers on my questions I am doing some assumptions, 1) Yes, 2) 0, 3) Yes. If I am wrong in my assumptions then please correct my suggested formula. I am using a nested IF formula just to be able to explain the logic better. You find the finished formula at the end.
First control, does cell in G column have a zero (no delivery)?
=IF(G3=0,
If yes then let's check if PO DUE date is greater than todays date.
=IF(G3=0,IF(E3>TODAY(),
If yes then there is no late delivery, let's put in a zero.
=IF(G3=0,IF(E3>TODAY(),0,
But if no then there is a late delivery, lets show by how many days.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),
Now the second IF condition is closed, we are back to the first IF condition and to where G3 does not have a 0, but a date, so let's check if that date is greater than PO DUE date or not with a third IF condition.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,
If G3 is greater then we want to see by how many days the delivery was late.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,
And if the date in G3 is lesser than or equal to PO DUE date, then just put in a 0, the delivery was in time.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,0)
As we now have completed all our controls, let's close the formula with the last right parentheses.
=IF(G3=0,IF(E3>TODAY(),0,TODAY()-E3),IF(G3>E3,G3-E3,0))
I hope this solves your issue.
VIP
April 21, 2015
Offline
Hi Mrashant Manjrekar, welcome to Mynda´s Forum.
I think when material has arrived it makes no sense to have a number in column F, right? Maybe a text as 'delivered' will do.
So I think in F3 the formula can be something like: =IF(G3<>0;"delivered",TODAY()-E3).
Or am I missing something?
Frans
VIP
April 21, 2015
Offline
Sorry Anders, we were answering same time I think so I didn't saw your post until I posted mine.
Frans
VIP
Trusted Members
December 7, 2016
Offline
Hello Frans,
Nothing to be sorry about, the more replies the better. It is always nice to see different solutions and approaches to a problem and yours is an example of keeping it simple, the first step to success. 
1 Guest(s)
