Multiple if statements based on dates

[StillUnderstanding]

Hello,

I wonder if someone might be able to helps me with an IF statement. I am looking to do the following:-

If the time between 2 dates is less than 14 then return the word "Yes", If the time between 2 dates is more than 14.01 then return the word "No", If the time is less than 14 from today then return "Not Included"

Having some issues trying to work this out and hope someone might be able to help?

 

[Haree]

Hi
less than 14 days, or time. If it is time do you have date and time combined together ?, Can you show an example please

 

[WillUK]

When you're saying "If the time is less than 14 from today", are you asking "if the 1st date is less than 14 days from today" or "if the last date is less than 14 days from today" and should this overwrite the Yes and No conditions ?

 

[StillUnderstanding]

So below is a likely output based:-

Date 1	Date 2	Today	Is the date less than 14 days?
01/05/2022​	11/05/2022​	29/06/2022​	Less than 14 days
01/05/2022​	19/05/2022​	29/06/2022​	More than 14 days
17/05/2022​	18/05/2022​	29/06/2022​	Under 14 days from today

So the example above asking if Date 1 is within 14 days of Date 2, if so then return "Less than 14 days" if more then return More than 14 days. The third variable comes in if todays date is less than 14 days from Date 1, if so then return "under 14 days from today"

I hope that helps?

 

[WillUK]

Try this :

=IF(SUM(TODAY()-A3)<14,"Under 14 days from today",IF(SUM(B3-A3)>14,"More than 14 days","Less than 14 days"))

Assuming Date 1 = Column A and Date 2 = Column B

 

[StillUnderstanding]

Try this :

=IF(SUM(TODAY()-A3)<14,"Under 14 days from today",IF(SUM(B3-A3)>14,"More than 14 days","Less than 14 days"))

Assuming Date 1 = Column A and Date 2 = Column B

Thanks @WillUK this works but I think I am now missing a match at the start. Here is the code that works without matching anything.

=IF(SUM(TODAY()-'Sheet1'!I33)<14,"Under 14 Days from Today",IF(SUM(H33-'Sheet1'!I33)>14,"More than 14 Days",IF(SUM(H33-'Sheet1'!I33)<14,"less than 14 days from column A")))

What would I need to do if I wanted to match an email address in both sheets first, so if the emails match then return the data, if they don't match then return "Blank"

So if the email address was on column AM in sheet 1 and AN in the sheet that the code would be in.

 

[Sufiyan97]

What would I need to do if I wanted to match an email address in both sheets first, so if the emails match then return the data, if they don't match then return "Blank"

So if the email address was on column AM in sheet 1 and AN in the sheet that the code would be in.

Where will be sheet 2 email?

 

[StillUnderstanding]

Where will be sheet 2 email?

So on Sheet 1 it would be column “am” and on the sheet with the code it would be column “an”. The sheet with the second email would be Sheet 2.

 

[Sufiyan97]

So on Sheet 1 it would be column “am” and on the sheet with the code it would be column “an”. The sheet with the second email would be Sheet 2.

So you want formula in column AN of sheet1

in Which column would be the email in sheet2?

 

[StillUnderstanding]

@Sufiyan97 Sorry I seen how I have added confusion.

The code as it is now looks like this:-

=IF(SUM(TODAY()-Sheet2!A4)<14,"Under 14 Days from Today",IF(SUM(B4-Sheet2!A4)>14,"More than 14 Days",IF(SUM(B4-Sheet2!A4)<14,"less than 14 days from column A in sheet 2")))

Sheet 1 looks like this:-

And Sheet 2 looks like this:-

So using the above example I would be looking for the code to sit in column "D" of Sheet 1 and for it to check Sheet 2 for a matching email address and then compare the date to tell me if it fits in with one of the following:-

• Under 14 Days from Today
• More than 14 Days
• Less than 14 days from column A in sheet 2

At the minute the code is not matching the email address first.

Thanks for all your help with this!