Hi,
I'm trying to add different series of years, months, and days together to obtain display a grand total like the one I have in bold below:
Example:
1 years, 10 months, 30 days
+
10 years, 6 months, 28 days
+
1 years, 5 months, 29 days
Intermediate Total = 12 years, 21 months, 87 days
Display Goal Total = 14 years, 11 months, 27 days*
*Note: Assuming 30 days = 1 month
Any help with this one is most appreciated!
Jay
Welcome to the board,
how is your data displayed ?
With the help of a few helper columns
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
How did you get your data? If they are the result of calculating the difference between 2 dates then it would be easier to use the base data, e.g.
If you have start dates in A2:A10 and end dates in B2:B10 and you want the total time in the format given then you can use a formula like
=DATEDIF(SUM(A2:A10),SUM(B2:B10),"y")&" years, "&DATEDIF(SUM(A2:A10),SUM(B2:B10),"ym")&" months, "&DATEDIF(SUM(A2:A10),SUM(B2:B10),"md")&" days "
Hi All,
I am in the military and have to track periods of service member's periods of service. Some of those members have "broken time", so their dates are not consecutive. Therefore, I need to add up each period of "good time" and then come up with an accurate grand total for the total "good time" they have served, which needs to be broken down by years, months, and days.
So, I am subtracting two dates to determine each period of good service.
While it is a bit clunky, I have been using the following formula to calculate and the display the difference between dates:
=YEAR(C72)-YEAR(B72)-IF(OR(MONTH(C72)<MONTH(B72),AND(MONTH(C72)=MONTH(B72), DAY(C72)<DAY(B72))),1,0)&" years, "&MONTH(C72)-MONTH(B72)+IF(AND(MONTH(C72) <=MONTH(B72),DAY(C72)<DAY(B72)),11,IF(AND(MONTH(C72)<MONTH(B72),DAY(C72) >=DAY(B72)),12,IF(AND(MONTH(C72)>MONTH(B72),DAY(C72)<DAY(B72)),-1)))&" months, "&C72-DATE(YEAR(C72),MONTH(C72)-IF(DAY(C72)<DAY(B72),1,0),DAY(B72))&" days"
For instance, if I am calculating the "good time" for this date range:
28-Aug-1981 TO 27-Mar-1992, the formula I have been using yields:
10 years, 6 months, 28 days
A member with "broken time" might have 2 or 3 periods of "good time" like the above. So after I obtain each "good time" period, which I display in columns, I then want to add all the periods of "good time" together to obtain a grand total of a member's "good time".
Haven't had a chance yet to try out the .zip file!
Thanks,
Jay
To calculate difference between two dates try :
For the nr of yearsFor the number of months
Please Login or Register to view this content.For the nr of days :Please Login or Register to view this content.Where A1 is the begin date and A2 the end date ( change the semi-coloms to commas if necessary)Please Login or Register to view this content.
This is the method found here :http://www.cpearson.com/excel/DateTimeWS.htm
Last edited by arthurbr; 02-01-2008 at 10:42 AM.
Trying to add text strings like "2 years, 3 months, 4 days" is unnecessarily complicated. Try using your base dates to calculate the total period as per my suggestion above.
Thank you for your help fellas!
I just had surgery, so will give your suggestions a go once I am feeling better and my mind is more clear!
Again, thank you and my apologies for not answering a bit quicker--I'll try all your suggestions in a couple of days and let you know I how I do.
With best wishes,
Jay
Daddylonglegs,
I just gave your solution a run and it worked like a champ! Many thanks for your help.
Likewise, thanks to everyone else who also lent a hand (formula)!
Jay
Hello!Originally Posted by daddylonglegs
I know this post has been made years ago but i still hope for a reply. I tried your formula on a data which has two date ranges and it worked. Now i do not know how to do it with a data that has three or more date ranges. I tried adding another SUM command for each of the year, months and days (ex.=DATEDIF(Sum(A2,A4),SUM(B2,B4),SUM(C2,C4),"y")&"years",...) but Excel returns an error message about the formula being too long or there are too many entries.
Hope you could help me.
Thanks,
Eds
Welcome to the Forum, unfortunately:
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 shows an error. The error is 2n-1 with n no. of values. The obtained value was 2n-1 less than actual value.
If I am wrong, can you please upload an excel sheet with an example.
It shows an error. The error is 2n-1 with n no. of values. The obtained value was 2n-1 less than actual value.
If I am wrong, can you please upload an excel sheet with an example.
It shows an error. The error is 2n-1 with n no. of values. The obtained value was 2n-1 less than actual value.
If I am wrong, can you please upload an excel sheet with an example.
This thread is 7 years old: please start your own thread.
@gillawat, I'm baffled at your post. You blatantly ignore the post before yours that explains that you should NOT post your own question in someone else's thread. You also post your own personal spin into a thread that was started more than ten years ago. Really? Please take a moment to gather your thoughts. If you then still have a question, please open a new thread in the appropriate sub-forum. You are welcome to post a link to any existing question that relates to your problem, but please don't post in decade old threads.
please help me how to find total year and month with below data
1years,9months
0years,9months
0years,11months
Please help me how to sum
1years,9months
0years,9months
0years,11months
total
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks