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 much

message edited by Freeman


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

Reply ↓  Report •

✔ Best Answer
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 H
2 - Open the Conditional Formatting wizard
3 - Select New Rule
4 - Select "Format only cells that contain"
5 - Choose "equal to" in the 2nd drop down
6 - Enter COMPLIED in the 3rd field
7 - Click the Format button and chose a format, such as a Fill Color
8 - OK your way back out until the wizard closes

Repeat 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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#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.


Reply ↓  Report •

#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


Reply ↓  Report •

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. hihi
I'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.


Reply ↓  Report •

#5
February 19, 2019 at 04:23:56
So, in other words:

1 - Starting on the due date and moving forward, status is DUE
2 - Starting one month before the due date and up until one day before the due date, status is CAUTION
3 - Prior to one month before the due date status is COMPLIED


Do 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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
February 19, 2019 at 06:13:19
So, in other words:

1 - Starting on the due date and moving forward, status is DUE
2 - Starting one month before the due date and up until one day before the due date, status is CAUTION
3 - Prior to one month before the due date status is COMPLIED

1- 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.


Reply ↓  Report •

#7
February 19, 2019 at 07:32:27
✔ Best Answer
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 H
2 - Open the Conditional Formatting wizard
3 - Select New Rule
4 - Select "Format only cells that contain"
5 - Choose "equal to" in the 2nd drop down
6 - Enter COMPLIED in the 3rd field
7 - Click the Format button and chose a format, such as a Fill Color
8 - OK your way back out until the wizard closes

Repeat 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


Reply ↓  Report •

#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


Reply ↓  Report •

#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! :)


Reply ↓  Report •

Ask Question