Jagwarman
asked on
Help with Excel Formula
Can an expert help me with a formula please, I have attached a file that hopefully will assist.
I need column T to either say Yes or No depending on the result.
Col L has a Day which populates when Sign Off date/Time is completed.
Col M has a Day [this is fixed].
Col N has a time [fixed]
Col O has a time [fixed]
Q and R are populated when Tick box in P is completed. [removed in this file]
Col S either Yes or No [with formula]
In col T I need formula that will say Yes or No based on:
If L & M are the same and the Time in Q is less than or equal to the time in O the answer in Col T is No
If L & M are the different and the Time in Q is less than or equal to the time in O the answer in Col T is Yes
deadline.xlsx
I need column T to either say Yes or No depending on the result.
Col L has a Day which populates when Sign Off date/Time is completed.
Col M has a Day [this is fixed].
Col N has a time [fixed]
Col O has a time [fixed]
Q and R are populated when Tick box in P is completed. [removed in this file]
Col S either Yes or No [with formula]
In col T I need formula that will say Yes or No based on:
If L & M are the same and the Time in Q is less than or equal to the time in O the answer in Col T is No
If L & M are the different and the Time in Q is less than or equal to the time in O the answer in Col T is Yes
deadline.xlsx
ASKER
Thanks but not sure you picked up on the fact that Cell O is Time only [i.e 15:00] and Q is Date and Time [i.e. 12/08/2015 15:29:59]
Regards
Regards
Your explanation for Yes and No is not clear. Why T8 should be Yes if T7 is a No.
Anyways try the following formula in T6 and copy it down.
Anyways try the following formula in T6 and copy it down.
=IF(Q6="","",IF(AND(MATCH(M6,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)<WEEKDAY(Q6),N6<O6),"No","Yes"))
Edit:
I think this should be the formula you need......
In T6
I think this should be the formula you need......
In T6
=IF(Q6="","",IF(AND(MATCH(M6,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)<WEEKDAY(Q6),(Q6-INT(Q6))<O6),"No","Yes"))ASKER
sktneer I agree my explanation for Yes and No is not clear
does this help
if the day due [Col M] was Mon and today [Col L] is Wed then the Day Deadline Missed should be Yes.
if the day due [Col M] was Thu and today [Col L] is Wed then the Day Deadline Missed should be No
does this help
if the day due [Col M] was Mon and today [Col L] is Wed then the Day Deadline Missed should be Yes.
if the day due [Col M] was Thu and today [Col L] is Wed then the Day Deadline Missed should be No
Oh. sorry about that. I think I misunderstood the question.
Did you try the edited formula I suggested?
ASKER
Hi sktneer, I did but it still does not give me the answer I am looking for.
That's still quite confusing.
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a No else Yes.
In other words if the the task is executed after the due day, it is missed and should display a No?
If so, have your shown the correct output in your sample file?
I think all your answers should be a Yes because all the executed days are greater than the due days.
BTW try the following formula, you will get a Yes for all of them....
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a No else Yes.
In other words if the the task is executed after the due day, it is missed and should display a No?
If so, have your shown the correct output in your sample file?
I think all your answers should be a Yes because all the executed days are greater than the due days.
BTW try the following formula, you will get a Yes for all of them....
=IF(Q6="","",IF(WEEKDAY(Q6)>MATCH(M6,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0),"Yes",IF(AND(WEEKDAY(Q6)=MATCH(M6,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0),(Q6-INT(Q6))>O6),"Yes","No")))
I think I wrote it in reverse order...
It should be actually this...
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a Yes (Missed) else No (Not Missed).
But the last formula remains unchanged.
It should be actually this...
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a Yes (Missed) else No (Not Missed).
But the last formula remains unchanged.
ASKER
sorry sktneer the more I look at it I am also confusing myself as well as you.
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a Yes (Missed) else No (Not Missed).
in answer to that question, yes
Does that simply means, If Executed Day (Col. L) is greater than the Day Due (Col. M), it should be a Yes (Missed) else No (Not Missed).
in answer to that question, yes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sktneer
thanks and sorry for all the confusion. Was away from work Thurs and Fri which is why I have only just answered.
thanks and sorry for all the confusion. Was away from work Thurs and Fri which is why I have only just answered.
No problem. Glad that your issue is resolved.
Open in new window