Ive been scratching my head for ages, i can calculate number of years,months and days betwen 2 date stamps
I can calculate the number of hours, minutes, seconds between 2 time stamps
BUT when i have 01/05/2017 12:27:45 MINUS 04/07/2010 13:45:34 how do i display this as X years, X months, X days, X hours, X minutes, X seconds?
Any help is appreciated!
For years =YEAR(B1)-YEAR(E1)
For days =DAYS(B1,E1)
For time =F1-C1
rikkyshh
Watch this Link
https://www.mrexcel.com/forum/excel-...s-minutes.html
I hope I have helped
Decio
The formula here was wrong. See explanation in next posts.
Last edited by Kaper; 10-21-2017 at 10:28 AM. Reason: deleted wrong formula
Best Regards,
Kaper
.....but this always gives you the wrong number of months (because it always gives you a value in the range 1-12 instead of 0-11 as required) e.g. when the actual difference is 20 days this shows 1 month and 20 days....Originally Posted by Kaper
It's possible to use the TEXT function for hours, minutes and seconds but you probably need something else for years, months and days, e.g.
=DATEDIF(A1,B1,"y")&" Years, "&DATEDIF(A1,B1,"ym")&" Months, "&DATEDIF(A1,B1,"md")&" Days"&TEXT(B1-A1," h"" Hours ""m"" Minutes ""s"" Seconds""")
Last edited by daddylonglegs; 10-21-2017 at 10:07 AM.
Audere est facere
Whoops, indeed - also days make problem (except 0 days). So indeed - wrong result.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks