Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculation on dates

0

I have a  2 given data- start date and end date.(2 column). 3rd column is the difference of start date and end date (which I need the answer to be either a 1 year or 2 years else shows "?". 4th column - using the end date to compare with computer system date- to show either "expired" or leave blank (meaning still within the contract period). 5th column I need to pick up those dates that are 1 mth before the computer system date and print "to inform".

Please assist me as I need this for my working schedule. 

\

excelformulas-functionsdate-time
MS Office Version: kywong
posted by: kywong (rep: 6) on Thu Apr 26, 2018 at 9:27 am
Answer
Discuss

Answers

0
Selected Answer

Presuming that your first column is column A and your second column is column B you might use the formula below in row 3 of your third column.

=IF($B3 = EDATE($A3,24), "2 years",IF($B3=EDATE($A3,12),"1 year","?"))

Note that this formula presumes that you calculate a year from Jan 1 to Jan 1. If your contract year ends on Dec 31 modify the comparison date in the formula to EDATE($A3,24)-1

Your fourth column might have this formula.

=IF($B3>TODAY(),"","Expired")

Note that it will not show the contract as expired on its last day of validity (the date in B3). If you want to change that modify the comparison operator to >=

For your fifth column I suggest the following formula.

=IF(AND(TODAY() >=EDATE($B3,-1),$D3=""),"to inform","")

Observe that it refers to cell D3 which is your fourth column to decide that it will not show "to inform" after the contract is expired. As an alternative, you might combine "to inform" and "Expired" into a single column with this formula.

=IF(TODAY()>$B3,"Expired",IF(TODAY() >=EDATE($B3,-1),"to inform",""))

Note that EDATE($B3,-1) specifies a date which is exactly 1 month before the date in B3. This date is compared with today's date in IF(TODAY() >=EDATE($B3,-1). You can modify the date to be compared. For example, EDATE($B3,-1)-5 would be a date 5 days before the date which is 1 month before B3. It may not be strictly necessary but I would recommend to enclose such a calculation in parentheses., thus (EDATE($B3,-1)-5).

Good luck!

posted by: Variatus (rep: 4889) on Thu Apr 26, 2018 at 9:03 pm
Discuss

Discussion

Variatus, thank you. will give it a go.
kywong (rep: 6) Apr 28, '18 at 9:11 am
Let me know how you make out. Upon reviewing my answer I find that you might wish to combine "to inform" and "expired" into one column. This is the formula that would do that job. 
=IF(TODAY()>$B3,"Expired",IF(TODAY() >=EDATE($B3,-1),"to inform",""))
Variatus (rep: 4889) Apr 28, '18 at 9:48 pm
Hi Variatus, thank you so much.  Am really happy with your help.  I prefer to combined the "to inform" & "expired" into a column.  Wonderful.  Now would you kindly let me know what if there is a blank date cell in Column A & B? Meaning a row is empty, then the result in column C and D will display error.

Also I would like the condition for "to inform" to be within the range of 30 to 35 days from expiry date.
Please help. Thank you
kywong (rep: 6) May 2, '18 at 9:29 am
I have ecxpanded my original answer to include instructions on how to modify the days of "to inform". I think, none of the formulas throws an error if one of the dates is blank. Errors will occur if they contain text, even a blank space. Consider the advantage of being alerted to such user errors but if you wish to hide them, embed the entire formula in an ISERROR() function, like =IFERROR([Formula without leading =], "") which would show a blank instead of an error or you can replace "" with "Error".
Please remember to mark the answer as accepted when it does what you originally wanted. Thank you.
Variatus (rep: 4889) May 2, '18 at 9:49 pm
Hi Variatus, very sorry I am new in this forum.  Would like you to tell me where to mark the answer as accepted.  Your answers is perfect.
kywong (rep: 6) May 3, '18 at 8:15 am
Hi variatus, I think I have click wrongly and a REMOVE ANSWER appear. Am very sorry.  What should I do to correct. Your answer is perfect.
kywong (rep: 6) May 3, '18 at 8:30 am
Hi variatus, I think I have click wrongly and a REMOVE ANSWER appear. Am very sorry.  What should I do to correct. Your answer is perfect.
kywong (rep: 6) May 3, '18 at 8:31 am
Looks like you got it fixed. Thank you for your concern. I really appreciate that!
Variatus (rep: 4889) May 3, '18 at 7:42 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login