Calculating years/months/days between two dates
my spreadsheet is setup with two dates:
A date for when someone joins our group
A date for when they are released/leave
I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success. Any help would be greatly appreciated.
Thank you,
Jen
have you tried
datedif
function
http://www.cpearson.com/excel/datedif.aspx
see the last part about birthdays
you can replace birthdate and today() with your start and finish dates
Last edited by martindwilson; 10-17-2009 at 09:47 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Or, if you're like me and enjoy doing things the hard way for marginal benefit, you can try this formula:
where A1 is the end date and A2 is the start date. A quick find & replace will fix all the references for you. The one advantage of this formula is that it's grammatically correct. If there is precisely a one-year difference, it will show "1 Year". If it is two years, three months, and four days, it will read "2 Years, 3 Months, 4 Days". If it is just three days, it will read "3 Days" and so on.
Please Login or Register to view this content.
I'm sure there are inefficiencies in it, so if there's a guru out there that sees an easy way to shorten this while still keeping its grammatical correctness, I'd be interested to see your corrections.
Darkyam,
I tried your formula and that worked perfectly!
Thank you so much,
Jen
@darkyam
Sir,
Your formula is very good one but there is a problem, i have entered in cell A2 date 15/03/1981 and cell A1 date 05/03/2012, no result appeared for this then i tried to take value on or 15th of any month and it started giving results, please tell me how to overcome this anomaly in the formula.
kind regards
abhishek nagar
Dayum! That's a long formula. I like how darkyam presented it tho: "doing things the hard way for marginal benefit"
Anyways, I was wondering why datedif was not taken into consideration as suggested by the member in post 2:
Beginning date is entered in A1, and the date difference is calculated between that date and Today's date. If you prefer to have a static ending date then just replace TODAY() with A2, and entered an ending date in A2.Please Login or Register to view this content.
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
Hello abhisheknagar, and welcome to the forum.
Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
How to calculate years/months/days between two dates in excel worksheet:
A date for when someone joins our group
A date for when they are released/leave
I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success. Any help would be greatly appreciated.
Thank you,
Sthabir
Sthabir,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hello,
My question is
"how to calculate years months and days between two dates in google spreadsheet"
Because the "=DATEDIF" is not getting accepted, it works in Excel Sheet
Tnx
Rafi
RAFIANTONY,
Please check post 9 in this thread.
if A is the start date and B is the end date, try this formula:
Please Login or Register to view this content.
Click on the star if you think I helped you
Good afternoon,
First time user. The formula provided by darkyam in this thread was very helpful and I was successful at calculating year/month/days between two dates. Can anyone provide assistance on the following?
For example:
Start-End #1 = 1 year, 5 months, 5 days
Start-End #2 = 5 years, 2 months, 10 days
What formula can I use to take the total sum of both calculations to show 6 years, 7 months, 15 days?
Obviously I can calculate this by hand no problem, but this spreadsheet will be given to over 300 folks and the easier I can make this the better.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks