Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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:
=EDATE(A1,120)

View Answers     

Similar Excel Tutorials

Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...
PMT Function - Get the Payment Due for a Loan in Excel
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
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
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
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

Similar Topics







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.

Thanks


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:

Code:

=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"
and
Code:

=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:

Code:

=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;
and
Code:

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

Thanks




HI All,
Just wondering if some of you gurus would mind helping me with a simple formula that i can't get my head around.
I have a date in cell A1 (dd/mm/yy) which represents an expiry date. I would like to have the word "expired" appear in cell A2 based on a formula which would subtract a number of months. eg. If the date in A1 is older than 10 months ago based on todays date then "expired" in A2. I imagine this would be based around the today() formula but i can't quite get it to work for me.
Many thanks in advance.

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

=EDATE(B1,(60))

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

DATEDIF(B2, TODAY(), "Y")

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.




I have a spreadsheet that calculates the years and months since the date in the previous column. The formula that I use is:

=IF(L21="","",DATEDIF(L21,TODAY(),"Y")&"."&DATEDIF(L21,TODAY(),"YM"))

This works very well. I have it set so that the result is displays as years.months - EG:
Start Date years.months
9/1/1998 15.0
9/1/2013 0.0
9/1/1999 14.0


I would like to be able to total the list (years and months) but I am having trouble doing so. Can anyone help?

Blpen

I NEED TO CREATE A SPREADSHEET CALCULATING THE YEARS OF SERVICE EACH EMPLOYEE
HAS WORKED WITH OUR COMPANY. BELOW IS THE FORMULA I USED TO CALCULATE THE
YEARS, MONTHS, AND DAYS FOR EVERYONE, BUT WE HAVE ONE EMPLOYEE WHO LEFT THE
CO. AND WAS REHIRED A FEW TIMES.

=DATEDIF(B29,C29,"Y")&"YEARS, "&DATEDIF(B29,C29,"YM")&"MONTHS,
"&DATEDIF(B29,C29,"MD")&"DAYS"

BELOW ARE THE DATES THAT SHE CAME AND WENT.
(B27) (C27)
02/01/1988 - 05/06/1988
(B28) (C28)
01/15/1989 - 04/24/1998
(B29) (C29)
02/01/1999 - 06/28/2006
HOW DO I CHANGE MY FORMULA TO CALCULATE THESE DATES?
PLEASE HELP!!!!







Hello All,

I did a post a couple of weeks ago looking for a formula that took information form my datasheet placed it into a table by matching the persons name with the certificate they had and only picking the most recent date... this formula that works great but after looking at the data I need to add two (or this might be three, i'm not sure) new criterias:

Here is the original post:
http://www.excelforum.com/excel-form...est-dates.html

And here is the solution that did everything that i wanted (Shout out to Fortis that solved this):


Please Login or Register  to view this content.


Here is a new excel example with the data and the two new criterias highlighted in yellow:

The first new criteria that I have is that I've added hyperlinks to all the certificates that I have which will show up in the LINK column as dates. Anything that I do not have a LINK to is blank and everything that I do is named after the expiration date that I had earlier.

What i would like to see in my results is that everything that does not have a link in column G (Certificate Database) place the date that it is expired along with some text "NC" (to signify No Certificate). I've highlighted the examples of the change on the following sheet...

The second criteria is that if the certificate date is in the past and expired to change the date to just "EXPIRED".

So these are seperate... but this maybe a third criteria that if it's expired and I don't have a certificate it then show "EXPIRED NC". Hope this makes sense...

Thank you all in advance!!!

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:

=IF(C1,D4>C1,D4<C1)

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

=IF(C1>D4,E1,F1)

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


Hi,

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.

thanks

Andrew


Hi,
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:

=(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1))-TODAY())/365.25

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.


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?




Good Afternoon,

Your assistance is once again greatly needed. and i will try to be specific.

I have Four Columns with Hundreds of Rows that contain data. Column A = Name of Document Column B = Issued Date Column C = Expired Date Column D = Verified
I would like the "Verified" Cell to Turn red when the "Expired Date" cell has Expired. Verification happens on a monthly basis (I am not sure if that is important)

Please help and let me know if i need to provide more data.

Many thanks in advance!
Sandra

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

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!


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

=IF(DATEDIF(TODAY(),M15,"d")<=0,"Expired",(DATEDIF(TODAY(),M15,"d")))

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,

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?

Thanks,

Aaron


Hello all.

=(A1-(NOW()))/365

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




Hello friends...

I am trying to write "IF" statement.... as per following logic....

1. Column A - Date of Expiry
2. Column B - NOC issued
3. Column C - Status as today (Status options, "Expired", "NOC" given and "Valid")

Condition 1: If Date is < than Date(today) then in Column C "Expired"
Condition 2: If in Column B "NOC" than in Column C "NOC" ( P.S. irrespective of Date of expiry)
Condition 3: If Date > than Date (today) then in Column C "Valid"
Condition 4: If Date > than Date (today) and in Column B "NOC" then in Column C "NOC"

=IF(1A<TODAY(),"Expired",IF(B1="NOC","NOC",IF(A1<TODAY(),"Expired","Valid")))

in above statement only two conditions are working...

Thanks in advance

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.

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


Hi

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


Hi i am working on a spreadsheet that should calculate the number of months remaining on a lease, or the number of months that have passed this is the information i have available

and what would be entered into the sheet
example?
LEASE A
start date 12/26/2009
Term : 44 months

I would like something that will tell me the number of months remaining on this lease , according to my calculations there should be 30 months remaining . what is the easist way to do this and just drag down the formula

further more, if the lease is expired or has less than 6 months left i would like it to return "expired"

Any help would be appreciated~ thanks


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?



Does anyone know if Excel has a formula that will highlight an expired date
in red based on the current date? Example. Start Date = 1/4/04 and End Date
= 1/3/05. Today = 1/4/05 therefore the End Date has now expired and I would
like that to show up in red text in order to flag expired dates. Any
suggestions? Thanks Donna



Hi

I'm trying to set up a formula in a cell that will highlight a date for a welder who has been qualified for welding, but is valid for 6 months. There is a date of record when he was qualified say 1/12/2009, but I have another column that I have added to say that it has expired (in Red) ie 30/06/2010. Put it simple, I'd like a cell to say that it has expired, or if it's not expired, to just show the date of expiry.

Regards