A project timer reports time elapsed in the format (y:d:h:m:s) e.g. 0:048:22:43:27
How do I format a cell in Excel to display the data in the same manner?
I then want a formula to calculate the difference between two cells with similar data with the result reported in similar format.
e.g. 0:050:23:58:35 - 0:048:22:43:27 = 0:002:01:15:08.
From my browsing of the forum I assume this will involve =DATEDIF which is a function I have never come across before.
Any help would be much appreciated.
Last edited by JonnieB; 12-19-2011 at 09:48 AM.
Hi and welcome to the board
Are you using decimal or sexagesimal notation for the time part?
How many days do your year notation count ? 360 or 365 ?
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Assuming start time in A2 and end time in B2 then use this formula in C2
=LEFT(B2,FIND(":",B2)-1)-LEFT(A2,FIND(":",A2)-1)-(RIGHT(A2,12)>RIGHT(B2,12))&":"&TEXT(MOD(MID(B2,3,3)-MID(A2,3,3),365)-(RIGHT(A2,8)>RIGHT(B2,8)),"000")&TEXT(MOD(RIGHT(B2,8)-RIGHT(A2,8),1),":hh:mm:ss")
That should even work from one year to another......assumes that years have 365 days
Audere est facere
Ignore the version below, I corrected some inconsistencies......
=LEFT(B2,FIND(":",B2)-1)-LEFT(A2,FIND(":",A2)-1)-(RIGHT(A2,12)>RIGHT(B2,12))&":"&TEXT(MOD(MID(B2,FIND(":",B2)+1,3)-MID(A2,FIND(":",A2)+1,3)-(RIGHT(A2,8)>RIGHT(B2,8)),365),"000")&TEXT(MOD(RIGHT(B2,8)-RIGHT(A2,8),1),":hh:mm:ss")
Audere est facere
Wow! I am seriously impressed. I would never have got there even after hours of trial and error experimentation.
Your solution works perfectly.
Thank you so much.
Post Deleted.
Last edited by JonnieB; 01-09-2012 at 04:19 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks