# Solved How to make an IF statement formula for dates

February 14, 2019 at 22:57:13
Specs: Windows 10
 i have a problem making an IF statement, i want to have a remarks that will appear CAUTION with filled color of Yellow in the column before a month of expiration date, then when it expires on the date and onward it will say DUE with color Red filled in the column and when it is not expires will be COMPLIED with green filled color in it.so this will be an example: Col. A1 - life = 1830 Col. B1 - life remaining = 1564 Col. C1 - life due on = 29-May-2023 Col. D1 - Remarks Thank you so muchmessage edited by Freeman

See More: How to make an IF statement formula for dates

February 19, 2019 at 07:32:27
 For the following solution, I am going to make an assumption, which I really don't like to do. I am going to assume that your LIFE REMAIN column is a calculated value resulting from something like this:E2 is formatted as a number and contains this formula:=F2-TODAY() If that is the case, then I'm going to offer 2 different options. This option assumes that a "month" is the same thing as 30 days:=IF(E2<=0,"DUE",IF(E2>30,"COMPLIED","CAUTION"))This next option uses exactly 1 month in order to determine the first date on which to return "CAUTION". In other words, the same day of the month. February 19, 2019 is one month before Mar 19, 2019, even though it is only 28 days.=IF(E2<=0,"DUE",IF(F2>EDATE(TODAY(),1),"COMPLIED","CAUTION"))Those 2 options should take care of the Text that you want to see in the Status column.If you also want the colors that you mentioned in your first post, you will have to add Conditional Formatting to that column.1 - Select Column H2 - Open the Conditional Formatting wizard3 - Select New Rule4 - Select "Format only cells that contain"5 - Choose "equal to" in the 2nd drop down6 - Enter COMPLIED in the 3rd field7 - Click the Format button and chose a format, such as a Fill Color8 - OK your way back out until the wizard closesRepeat those same steps for CAUTION and DUE, choosing a different format for each.Let me know if you have any further questions.message edited by DerbyDad03

#1
February 15, 2019 at 03:55:15
 I'm confused.Any time up until the date in column A it has not expired, right? Therefore it should be green. Yet you want it to be yellow "before a month of expiration date". You can't have both.I think I know what you mean, but I don't want to work on a solution based on a guess. Please explain your requirements a little more clearly. Perhaps some examples will help. If you are going to post example data, please read the instructions found at the following How-To link.

#2
February 17, 2019 at 21:04:09
 ```im so sorry for my previous question. this is my 1st time asking here in the forum. my bad. So, i made a data here. hihi i have a Moving Date that updates my calendar everday. Then, in my status column. id like the CAUTION, DUE and COMPLIED will show. CAUTION will show a month before his due date DUE will show when it is on his due date COMPLIED when it is not his due Moving Date ---> February 18, 2019 A B C D E F 1 DESCRIPTION ACTION LIFE DATE INSTALL LIFE REMAIN DUE ON 2 Registration RENEWAL 1830 May 25, 2018 1561 May 29 2023 3 Airworthiness RENEWAL 365 June 05, 2018 106 Jun 04 2019 4 License RENEWAL 1079 Aug 15, 2018 892 July 29 2021 G H 1 LIFE SINCE INSTALL FLAG STATUS 2 269 3 258 4 187 Hopefully you could understand this data. I am very much appreciated with the help you are doing. Thank you so much. ```

#3
February 18, 2019 at 19:27:54
 I understand your data, but I still don't understand you requirements. Please clarify.```Moving Date ---> February 18, 2019 CAUTION will show a month before his due date```A month before his due date is January 18, 2019. Does this mean that on January 18, 2019 and only on January 19, 2019 CAUTION should show?If CAUTION should show on any other dates, please explain.`DUE will show when it is on his due date`So DUE should show only on February 18, 2019, correct?`COMPLIED when it is not his due`I assume you mean "not his due date"This one causes me the most confusion. Unless the Moving Date and the Due On date are the same, it will always be "not his due date". In other words, from the beginning of time up until February 17, 2019 and from February 19, 2019 until the end of time, it is "not his due date", therefore, according to your statement, it should show COMPLIED.I'm pretty sure that that is not what you want, so you'll need to explain your requirements again.message edited by DerbyDad03

Related Solutions

#4
February 19, 2019 at 01:15:02
 Aw, i thought it would help if i also put the moving date for your formula. hihiI'm sorry again, it makes you confuse. In my data sir which is in the A2 (Registration) the date is due on May-29-2023, a month before that is April-29-2023. So when my moving date hits April-29-2023, the CAUTION will appeared on the flag status of the registration.Then, when the moving date hits the date due on May-29-2023 onwards of the day it will appear the DUE.Unless, when i changed the dates of the Date Install and DUE ON date. Yes sir, the COMPLIED in the flag status will appear if it not his due date.

#5
February 19, 2019 at 04:23:56
 So, in other words:1 - Starting on the due date and moving forward, status is DUE2 - Starting one month before the due date and up until one day before the due date, status is CAUTION3 - Prior to one month before the due date status is COMPLIEDDo you see how my #3 is very different from "COMPLIED in the flag status will appear if it not his due date." "Not his due date" includes every other date, before and after his due date. I'm sure that that is not what you meant.I will work on this and offer you a possible solution.

#6
February 19, 2019 at 06:13:19
 So, in other words:1 - Starting on the due date and moving forward, status is DUE2 - Starting one month before the due date and up until one day before the due date, status is CAUTION3 - Prior to one month before the due date status is COMPLIED1- Yes sir.2- Yes sir, correct also.3- Yes sir. Prior to one month before the due date is COMPLIED, From May-25-2018 of date install up to April-28-2023 the flag status is COMPLIED. Because April-29-2023 up to May-28-2023 the flag status is CAUTION. Then, DUE flag status from May-29-2023 and moving forward."Not his due date" includes every other date, before and after his due date. I'm sure that that is not what you meant. (Yes sir, this is not what i meant.)I am very thankful for your patience and understanding. Thank you so much sir. I really appreciated your help.

#7
February 19, 2019 at 07:32:27
 For the following solution, I am going to make an assumption, which I really don't like to do. I am going to assume that your LIFE REMAIN column is a calculated value resulting from something like this:E2 is formatted as a number and contains this formula:=F2-TODAY() If that is the case, then I'm going to offer 2 different options. This option assumes that a "month" is the same thing as 30 days:=IF(E2<=0,"DUE",IF(E2>30,"COMPLIED","CAUTION"))This next option uses exactly 1 month in order to determine the first date on which to return "CAUTION". In other words, the same day of the month. February 19, 2019 is one month before Mar 19, 2019, even though it is only 28 days.=IF(E2<=0,"DUE",IF(F2>EDATE(TODAY(),1),"COMPLIED","CAUTION"))Those 2 options should take care of the Text that you want to see in the Status column.If you also want the colors that you mentioned in your first post, you will have to add Conditional Formatting to that column.1 - Select Column H2 - Open the Conditional Formatting wizard3 - Select New Rule4 - Select "Format only cells that contain"5 - Choose "equal to" in the 2nd drop down6 - Enter COMPLIED in the 3rd field7 - Click the Format button and chose a format, such as a Fill Color8 - OK your way back out until the wizard closesRepeat those same steps for CAUTION and DUE, choosing a different format for each.Let me know if you have any further questions.message edited by DerbyDad03

#8
February 19, 2019 at 09:39:55
 I hope you saw the edited version of my suggestion. The one with this formula:=IF(E2<=0,"DUE",IF(F2>EDATE(TODAY(),1),"COMPLIED","CAUTION"))I originally posted a much longer formula and then realized my mistake.message edited by DerbyDad03

#9
February 19, 2019 at 10:06:55
 For the following solution, I am going to make an assumption, which I really don't like to do.( I AM REALLY SORRY FOR MAKING YOU THIS, I DO)I am going to assume that your LIFE REMAIN column is a calculated value resulting from something like this: (You are correct, indeed with your formula)E2 is formatted as a number and contains this formula: =F2-TODAY()(THOSE FORMULAS YOU'VE GIVEN ME, IT REALLY FITS TO MY REQUIREMENTS AND CROSS CHECK WITH THE DATES IF IT APPEARS COMPLIED, CAUTION AND DUE)=IF(E2<=0,"DUE",IF(E2>30,"COMPLIED","CAUTION"))=IF(E2<=0,"DUE",IF(F2>EDATE(TODAY(),1),"COMPLIED","CAUTION"))THANK YOU SO MUCH FOR THE TIME YOU'VE SPENT WITH ME FOR THIS PAST FEW DAYS.I WONT FORGET THIS HELP YOU DID TO ME. MORE BLESSING! I AM SO HAPPY! :)