Email:      Pass:    Pass?

E-mail:

# Calculate Years, Months, Day

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)

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

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

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

Hi guys,

I have a specific problem that I can't work out regarding conditional formatting.

I have a spreadsheet that contains dates of when people conducted various training courses. These courses are lifed, so that after 3 years the courses need to be re-taken.

In the spreadsheet I 'need' to put the date the course was taken, and I would like the cells to turn yellow when they are within 6 months of expiring and then red once the course has expired.

The current formatting I am using is as follows:

=DATEDIF(B2-DAY(B2)+1,TODAY()-DAY(TODAY())+1,"d")>1095

Where B2 is the start of my range, and 1095 is the days in 3 years

and...

=DATEDIF(B2-DAY(B2)+1,TODAY()-DAY(TODAY())+1,"d")>912

Then obviously 912 is 2.5 years

So the issue I have is. If I add a date of 25-Dec-11 (which as today is 23-Dec-14, is between 2.5 years and 3 years, so should be yellow) it shows as red, and only changes to yellow if I put the date in Jan-12.

Is there are way of setting the CF so that it will change on the specific day, and not only once that month has passed?

Kind regards
Glenn

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)))
```

and
Code:

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

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.

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?

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

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.

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)

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!

Hi all,

I need a simple date checking formula. The conditions are below.

In the yellow coulmn there is Y marked considering "Yes" because the date in the blue column is expired. So I need a formula to show "OK" in the orrange column if the date is expired and Y" marked. If the date is expired and there is No "Y" in the yellow column, than the third column will show "clash".I am trying to find that the dates are organizing with "Y" or not.

i am attaching the file.

Hi All

Newbie to using Excel beyond a very basic level and would appreciate any help with this. I am creating rules in a consumables log to alert when expiry dates have arrived.

I have been able to achieve this using the following rule:

=IF(DAYS360(A1,TODAY())>=0,"EXPIRED","")

I then use conditional formatting to change the fill and font when "EXPIRED" text appears to make the field stand out.

I would also like to modify the rule to create an alert in the same field when an expiry is approaching (10 days before expiry up to the day before = "Soon to Expire"), followed by the "Expired" comment on the day of expiry.

I have attempted this by nesting additional IF functions without success.

Not sure if I'm taking the right approach here so any help would be greatly appreciated.

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?

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

I have a question regarding date intervals. I have a file that contain columns witch they are "service life " , " manufacture life " , " date of manufacture " , " date of install " , " date of expired "

Service life = B2 = should be in digits like 123 but means months
Manufacture life = C2 = should be in digits like 123 but means months
Date of install = D2 = SHOULD INTERE ON DATE LIKE MAR-01
Date of manufacture = E2 = SHOULD INTERE ON DATE LIKE MAR-01
Date of expired = MY QUESTION IS HOW I CAN CALCULATE THE DATE OF EXPIRED DEPENDS ON THE FOLLOWING STIPS

IF THE MFG LIFE IS 60 = 5 YEARS & THE MFG DATE IS FEB-02
5 + FEB-02 = FEB-07
IF THE SERVICE LIFE IS 108 = 9 YEARS & THE INSTALLED DATE IS MAR-03
9 + MAR-03 = MAR-2012

SO THE NEARST EXPIRATION DATE IS FEB-07

PLZ HELP ME 2 MAKE A FURMOLA THAT COMPAIRE THE 2 DATES AND GIVE ME THE NEARST DATE ON CELL F2

I ATTACHED A ZIP FILE CONTAINS MY SHEET

******** ******************** ************************************************************************> Microsoft Excel - New Microsoft Excel Worksheet.xls ___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout H15 =
A B C D E F 1 part*# service*life mfg*life date*of*install date*of*mfg date*of*expired 2 ????? 108 60 Mar-03 Feb-02 PLZ*HELP*ME Sheet1 *
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. [/img][/list]

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

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.