Email:      Pass:    Pass?

Free Excel Forum

Calculate Years, Months, Day

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

hi guys! good morning/eve.....! im a new member and i want really your help...i have a problem here.

example in:
full date in A1. example 1-jan-11 and in b2=i want in B1 add 10 years but (i have the formula) if B2 is blank i want to change it to "-" or any char until i fill up the A1.

1-Jan-11 expired: 1-Jan-21
but if i delete the date in A1 here is the result

_______________ expired: 31-Dec-09 <-- i want this to change "-" or any word until i fill up the A1...hope you guys get my problem.
this is the formula that i use:

Similar Excel Video Tutorials

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display

Similar Topics

Good morning/evening to all admin, staffs & members here..!

i have a problem here that i can't properly insert this formula:

here is the formula that i want to insert


without the "EDATE(D9,120))"

and this is the main formula:

D9 cell

the problem is... if the A9 AND B9 cell is empty the result in D9 will be "0"

i want this "0" to be change in "-"... how can i insert the first formula given above in the second formula? i try many times but failed...

i attached example workbook here for clearing my explanation (my English is bad)

Hi All

I have products that have a 3 year shelf life from date of package, I've got:
Col A: code of product.
Col B: date of package.
Col C: expiry date.
Col D: I would like to construct an if statement which will state:

Condition 1: that if expiry date (Col C) is less than current date (today()) then to display "expired by x years, y months and z days" with reference to the current date - using pearson's DATEDIF formula:

"Expired by "&DATEDIF(C2,TODAY(),"y")&" years "&DATEDIF(C2,TODAY(),"ym")&" months "&DATEDIF(C2,TODAY(),"md")&" days"

Condition 2: that if expiry date (Col C) is greater than current date (today()) then to display "in date - x years, y months, z days left to expire"

I'm unsure as to how to construct the second formula - as it will be a countdown date difference from the current date to the expiry date?

Final if statement will be:
If(C2<=Today(),formula for condition 1 as shown above,formula for condition 2)

Sample book attached

I am trying to find a formula that will calculate the amount of service a member has accrued.

The dates are inclusive so it is as not as straight forward as it first seems.

As an example I am currently trying to calculate the number of complete years and months between 01/12/1992 and 30/06/1996.

The formula I am using is:

=INT(DATEDIF(I9,K9,"m")/12) & " years" &MOD(DATEDIF(I9,K9,"m"),12) & " months"

This gives me a result of 3 years and 6 months when it is infact 3 years and 7 months (December 92 and June 96 are complete months).

To help you understand:
01/12/1992 - 31/12/1992 = 1 month
01/01/1993 - 31/12/1995 = 3 years
01/01/1996 - 30/06/1996 = 6 months

Total is 3 years and 7 months

Any help at all would be appreciated.


I am running into problems when trying to calculate months and years (separately) between two dates.

C9 = 12/31/2009
C10 = 1/31/2009

This being after 1/31/2010 there is 1 full year between today and C10 and 0 full years between today and C9. Additionally, this being 4/7/2010 there are 15 months between today and C10 and 3 full months between TODAY() and C9.

I am using the following:


=IF(ISBLANK(C9),(IF(ISBLANK(B9),"","NO END DATE")),(IF((DAY(NOW())>=DAY(C9)),0,-1)+(YEAR(NOW())-YEAR(C9)) *12+MONTH(NOW())-MONTH(C9)))

which returns "3"

=IF(ISBLANK(C9),(IF(ISBLANK(B9),"","NO END DATE")),(IF((DAY(NOW())>=DAY(C9)),0,-1)+(YEAR(NOW())-YEAR(C9))))

which returns "0"

OK... so everthing's good so far... HOWEVER:


=IF(ISBLANK(C10),(IF(ISBLANK(B10),"","NO END DATE")),(IF((DAY(NOW())>=DAY(C10)),0,-1)+(YEAR(NOW())-YEAR(C10)) *12+MONTH(NOW())-MONTH(C10)))

returns 14 instead of 15;

=IF(ISBLANK(C10),(IF(ISBLANK(B10),"","NO END DATE")),(IF((DAY(NOW())>=DAY(C10)),0,-1)+(YEAR(NOW())-YEAR(C10))))

returns "0" instead of "1"

I've switched TODAY() with NOW() and vice versa with no change.

The B9 and B10 values are start dates of a time period (B9=1/1/2009, B10=3/1/2007). I've only included them in my equation so that I could fill my equation down and rows that have no start and end dates were blank instead of showing an error. Obviously rows with a start date but no end date return a "NO END DATE" message.

I'm pretty new at this, so if anyone can help me out that'd be great! FYI, the purpose of this is for compounding interest based upon several different calculations. I only want FULL months and FULL years to be considered.


Good Morning I hope someone is able to help.

I have a spreadsheet that contains an issue date.

say this date is in B1, in B2 i have


this provides me with a date 5 years later.

Now in the next column B3 i want a countdown so it tells me how many years and months there is untill the date is reached.

I tried using


to give me how many years there are left

but this doesnt seem to work, can anyone provide me with any ideas of how to have a column telling me how long until a specific date.

I think the problem with the above is that when i refer to B2 it picks up the formula and not the cell value that is calculated.



(B27) (C27)
02/01/1988 - 05/06/1988
(B28) (C28)
01/15/1989 - 04/24/1998
(B29) (C29)
02/01/1999 - 06/28/2006

It's driving me insane.

I have my nice little spreadsheet listing the dates of purchased products and the date their warranty expires.

On the top row I have the =TODAY() function to insert today's date.

In the column next to the warranty expiry date I want to insert an IF= function that will return the values "In warranty" or "Warranty expired". I can only get it to return "true" or "false". It's driving me mad. The formula I have is:


C1 is =TODAY()
D4 is the warranty expiry date

I've tried putting the words "in warranty" and "warranty expired" into empty cells, and then using the formula


C1 is =TODAY()
E1 is "in warranty"
F1 is "warranty expired"

Only problem is it doesn't calculate the expiry properly. All cells with dates in are set up as date cells, rather than general or number.

I just can't fathom this out! Can anyone please help? TIA


Thanks for your help in advance. I'd like some help with the following.

As part of my vba code I am trying to delete any expired trades. In other words, Id like to delete any rows that have a date equal to today or older in the 'Expiration Date' column.

This is the code I use; but I have observed that it doesnt get rid of all old trades. Theres still a few which arent deleted. Can someone tell me what I'm doing wrong?


' Delete expired trades

    Dim myrange, cell As Range
    Set myrange = Sheets("Outstanding Confirmations").Range(Range("P2"), Range("P65536").End(xlUp))
    For Each cell In myrange
        If cell.Value <= Date Then
        End If
    Next cell



A few things that I would appreciate assistance with.

I need to display the date a policy was enacted, and then a date three years from that date to show expiry.

I can do TODAY(), but how do I display a date + 3 years.

Secondly, in a seperate cell, I would like text to appear if we are within 3 months of the expiry, and display "Expiring Soon", or if the date is passed, it should say "Expired". If the policy is in date, it can say "Valid".

I will then apply conditional formatting to apply colour.

hope you follow.



I need to calculate the difference in Years, Months and Days between:

Date 1 = TODAY()
Date 2 = 4 years after a date in cell A1, which will always be earlier than today's date

(A bit of backround - I have certain risk management procedures that have a lifespan of 4 years. I want to calculate the time between now and 4 years after the date the procedure was completed, essentially to see how long before they have to be redone).

So far I have:

=DATEDIF(A1+4,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

But that returns #NUM!.

Removing the +4 obviously just calculated the difference between the date in A1 and today, but I need the date in A1 PLUS 4 years and today.

I have also tried:


which works in theory, however:
a) no consideration for leap years
b) does not return nY, nM, nD - only the decimal.

However I would be happy to use this method if I could convert it to Years Months Days.

Any help would be very much appreciated. Thanks.

Hi all,

Is there a way of having the words "Expired" or "Live" appear in a cell adjoining a date?
The word "Live" would appear for dates in the future and the word "Expired" would appear for dates in the past.
And, if possible, I'd like to have the word "Expired" appear in black and the word "Live" appear in red.

Many thanks.

I'm attempting to build a relatively simple workbook to track when our engineers need to renew their various licenses.

In "column A" I have names (starting at "row 2"), in "row 1" are the licenses and certifications (starting at "column C"). In the same row as a persons name will be the expiration date of a particular license, in the column corresponding to that license. In "column B", I'd like a function that will look at all those dates and determine if any of them are past (IE, expired). If they are, display a note saying expired (then up the user to scroll across and determine which one).

Looking at a single cell isn't a problem (=IF(Today()-C2>0, "Expired", ""). I want to look across a range of cells, say C2 to M2, and determine if any of them expired.


- B

I have a simple worksheet which contains dates for specific events through out the years. Each event has an expiry date (2 yrs), so I would like to test D3 to Q16 (RangeName: "EventDates") to see if they have expired, and if so they will change Font Color to RED otherwise they will remain in BLUE.

ie: if an event was done back in 22-Jan-05, it's due date would be 22-Jan-07. So it means that based on today's date, it has expired already, so instead of having a blue font, it will then change to Red.

Can anyone help out with this one?


I've written this date formula to tell me how long someone has stayed from the date they moved in.


=IF(L14<>"",(DATEDIF(L14,NOW(),"y")&" Years, "& DATEDIF(L14,NOW(),"ym") &" Months, " & DATEDIF(L14,NOW(),"md") & " Days"),"")

I would like it to ignore the years/months/days if they are 0 - e.g. 2 years 4 days rather than 2 years, 0 Months and 4 days - is there a way of doing this?


I am working in an spreadsheet, that I use to upload information.
I need to use a formula/function that follows this rules.

If "E2" is the word "Fiction" then the result in M2 will be "377"
If "E2" is the word "NonFiction" then the result in M2 will be "378"
If "E2" is the word "Children" then the result in M2 will be "279"
If "E2" is the word "Textbooks" then the result in M2 will be "2228"
If "E2" is the word "Cookbooks" then the result in M2 will be "11104"
If "E2" is the word "Magazine" then the result in M2 will be "280"

It may be something simple, but I don't have much experience in formulas or functions , I know you guys are great, and I hope you can find me a easy solution.


Hello, i'm having a problem here. In a column I have a whole list("Dates") of various dates with different years. The trick is, I need excel to automatically adjust the value of the column("Status") next to the dates. For example, the requirement is if the row in the "Dates" column is 30 Apr 09, I want excel to add text into the "Status" column if the current date is 3 months or within 30 Apr 09.

I also need it to show if it's expired(past current date), or if it has 1 year or more to go(as of current date).

Please try to help me with this problem, and I really really appreciate all efforts and solutions. Thanks.

I am trying to make a cell that if it is past the date in another cell (K2) then it will display the word "EXPIRED." BUT will display nothing if it has not reached that date or no date was entered. I'm having trouble with nested IF AND statements. Here is what I have:

=IF(AND(K2<DATE, K2<>""),EXPIRED,"")

The date in K2 is in X/X/X format. I'm stumped and rather inexperienced in Excel. Thanks!

So this is a little strange and I am having difficulty figuring out how to calculate these numbers. I have multiple sheets with active dates on them under this code:

=(IF(J18="",0,DATEDIF(J18,TODAY(),"md")&" days , "&DATEDIF(J18,TODAY(),"ym")&" months, "&DATEDIF(J18,TODAY(),"y")&" years

This gives me a current break down of months days and years a project is running in a single cell.

Then on a different sheet I want a running total of the projects that are 0-2 months old, 3-5 months old, 6-8 months old, 9-11 months old, and then a year and older. So I did this formula for counting one of the months.

=+SUM(COUNTIF(January!N17:N4010,"*6 months*") which works great, the problem is if something is 1 year and 6 months old or 2 years and 6 months old it is also counted in this number. Is there a way I can just calculate the ones that are 6 months and not those that are 1 year and 6 months etc.


I am having a problem with dates. I am setting Cell "A1" to 11/1/2007 for the completition date of a project.

Below in cell "A10" I want the 11/1/2007 date listed and the following series dates filled to the right. (Example Nov-07,dec07,Jan-08 etc..)

I know that I can automatically fill the monthly date series in with the auto fill feature, however, if I were to change the value in Cell "A1" the dates listed after cell "A10" do not change. It is like they are no longer linked.

So I then tried to use the edate formula and that only worked when I manually plugged in the date 11/1/2007 into the formula bar with +1month. It didn't work when I plugged in cell "A1".

Any suggestions?

Hello all,

I was working on a tracker for my office and have to track about 42 peoples qualification dates. So I set it up to show how many days they have left by inputing "=DATEDIF(NOW(),'Maint. Qualification Due Dates'!E7:E33,"D")" it works great except for the fact that when the date exceeds 0 (for example, -1 or -2 days difference) it displays "#NUM!" rather then just 0. Is there something that I can add to the formula that will make it say "Expired" or 0 so that I know they are expired?

I assume that I might have to use the IF function in there?



Am using the following formula to show highlight expired records (Over certain date rance), & otherwise show how many days are left to expiry


Colum M contains the expiry date. If =DATEDIF returns less then or equal to 0 days, I want the cell to show "Expired" & otherwise show how many days are left to expiry

It's worked fine for records which haven't yet expired but returns #NUM! for all expired records instead of "Expired"

What have I done wrong???

Hello all.


The above formula gives me years remaining as "X.XX YEARS" (currently I have the cell formatted as 0.0" years"; )

This is a two-part question:
(a) FORMULA CHANGE: I want to change the formula so that the result is the same, UNLESS [LESS THAN 1 YEAR], THEN "X.XX MONTHS"

(b) FORMATTING METHOD: should i set up a conditional formatting solution, or can I just do this all in one in-cell formula?

Any help would be much apprecieted.

- Jim


I am tyring to show the difference between 2 dates as text using the formula below:

Start Date End Date
01/01/2007 31/12/2008
=DATEDIF(A$2,B$2,"y")&" years, "&(DATEDIF(A$2,B$2,"ym")&" months, "&(DATEDIF(A$2,B$2,"md")+1)&" days.")

but I am getting the result:

1 years, 11 months, 31 days.

How can i get the result to say 2 years, 0 months, 0 days?

Thank you

We are using the formula =DATEDIF(A1,NOW(),"y") & "." &
DATEDIF(A1,NOW(),"ym") to calculate the years and months from an employee
hire date.

Is there an easy way to average the years and months for a group of
employees with the results of that formula?

Dear friends,

Please help me, its been so long since the last time I pay attention to my teacher in regards to the =If() Formula.

I have a problem now, I am currently making a report for our Accreditations and Certifications. Now, my boss wants me to make a list of all our accreditations and certifications. He requested me to make a list which will warn us (the word "Renewal Due" in different color") if our Certificates reach 3 months before the expiry.

I hope you got my point, again. I need a formula which will warn me, if our certificates will gonna expired.

For example:
Certificate No. 15-100
Issue Date: July 1, 2008
Expiry Date: June 30, 2011

Now, the Status between July 1, 2008 and March 31, 2011 will be CURRENT
If it will reach April 1, 2011 the Status will change to RENEWAL DUE
If it will reach to July 1, 2011, the status will be EXPIRED

I hope you can help me. This is making me cry...
I know, all of you here are experts. I just wish I maintained my notes from school.

Thanks in advance.

PS. Sorry if I posted this in the wrong thread.

3 months before the expiry date (April 1, 2011)