# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can try this formula in cell T6

``````=IF(L6 = M6, IF(Q6 <= O6, "No", ""), IF(Q6 <= O6, "Yes", ""))
``````
Author Commented:
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
Excel & VBA ExpertCommented:
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.

``````=IF(Q6="","",IF(AND(MATCH(M6,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)<WEEKDAY(Q6),N6<O6),"No","Yes"))
``````
Excel & VBA ExpertCommented:
Edit:

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"))
``````
Author Commented:
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
Commented:
Oh. sorry about that. I think I misunderstood the question.
Excel & VBA ExpertCommented:
Did you try the edited formula I suggested?
Author Commented:
Hi sktneer, I did but it still does not give me the answer I am looking for.
Excel & VBA ExpertCommented:
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....
``````=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")))
``````
Excel & VBA ExpertCommented:
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.
Author Commented:
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
Excel & VBA ExpertCommented:
If that assumption is true, the following formula should be the correct formula and you will get Yes for all the cells in col. T as per the condition because all the executed days are greater than due days.
``````=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")))
``````

Experts Exchange Solution brought to you by