Calculating days/hours elapsed

**JonnieB** · 01-09-2012, 04:17 PM

This is a follow on to my previous thread.

A project timer reports time elapsed in the format (y:d:h:m:s) e.g. 0:048:22:43:27
(year = 365 days)

I want to add 2 results together.

0:048:22:43:27 + 0:002:01:15:08 = 0:050:23:58:35
Cell A2 Cell B2 Cell C2

Previous response by Daddylonglegs for subtraction of one result from another was:

=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")

This works just fine but I'm struggling to convert it to handle addition. Can someone put me right please?

**Richard Buttrey** · 01-09-2012, 04:48 PM

Hi,

=LEFT(B2,FIND(":",B2)-1)+LEFT(A2,FIND(":",A2)-1)&":"&TEXT(MOD(MID(B2,3,3)+MID(A2,3,3),365),"000")&TEXT(MOD(RIGHT(B2,8)+RIGHT(A2,8),1),":hh:mm:ss")

**JonnieB** · 01-09-2012, 05:17 PM

Thanks Richard, much appreciated.

Now I can go back and try to understand why I couldn't get it right.

**Richard Buttrey** · 01-10-2012, 06:25 AM

Hi,

Use this one instead of my previous version which allows for days totalling > 365 and hours > 24

=LEFT(B1,FIND(":",B1)-1)+LEFT(A1,FIND(":",A1))+IF(MID(A1,3,3)+MID(B1,3,3)>=365,1,0)&":"&TEXT(MOD(MID(B1,3,3)+MID(A1,3,3),365)+IF(RIGHT(A1,8)+RIGHT(B1,8)>1,1,0),"000")&TEXT(MOD(RIGHT(B1,8)+RIGHT(A1,8),1),":hh:mm:ss")

**JonnieB** · 01-10-2012, 06:31 AM

Thanks Richard - your amended formula appears to have solved the problem.

**JonnieB** · 01-10-2012, 06:59 AM

Sorry I spoke too soon!

The amendment appears to handle the addition correctly, but the format of the "y:" part of the result is askew.

Applying the formula to:

1:364:00:00:00 + 0:002:00:00:00

returns 1.04166666666667:001:00:00:00

instead of 2:001:00:00:00

**Richard Buttrey** · 01-10-2012, 08:21 AM

Try the following

=TEXT(LEFT(B5,FIND(":",B5)-1)+LEFT(A5,FIND(":",A5)-1)+IF(MID(A5,3,3)+MID(B5,3,3)>=365,1,0),"0")&":"&TEXT(MOD(MID(B5,3,3)+MID(A5,3,3),365)+IF(RIGHT(A5,8)+RIGHT(B5,8)>1,1,0),"000")&TEXT(MOD(RIGHT(B5,8)+RIGHT(A5,8),1),":hh:mm:ss")

**JonnieB** · 01-10-2012, 11:24 AM

The final amendment seems to have cracked the problem. My friends over on WorldCommunityGrid.org (the source of the data), and I, thank you for your efforts,

**JonnieB** · 01-11-2012, 01:03 PM

The formula works well on daily data but fails when "y:" >9. "y:" can be as high as 7900.

1:200:03:01:40 + 590:017:00:07:11 returns 591:200:03:08:51 correct value is 591:217:03:08:51
1:112:22:43:16 + 248:221:16:04:38 returns 249:113:14:47:54 correct value is 249:334:14:47:54
0:065:16:20:41 + 15:240:01:46:36 returns #VALUE! correct value is 15:305:18:07:17

Can the formula be amended to accept any value of "y:" ?

Is it possible to construct a formula to sum a range of data in the format (y:ddd:hh:mm:ss) "y:" being of variable length?

**Richard Buttrey** · 01-11-2012, 05:36 PM

Hi,

Apologies for that. Try the following

=LEFT(A2,FIND(":",A2)-1)+LEFT(B2,FIND(":",B2)-1)+IF(VALUE(MID(A2,FIND(":",A2)+1,FIND(":",A2,6)-FIND(":",A2)-1))+VALUE(MID(B2,FIND(":",B2)+1,FIND(":",B2,6)-FIND(":",B2)-1))>365,1,0)&":"&MOD(MID(A2,FIND(":",A2)+1,FIND(":",A2,6)-FIND(":",A2)-1)+MID(B2,FIND(":",B2)+1,FIND(":",B2,6)-FIND(":",B2)-1),365)+IF(MID(A2,LEN(A2)-7,2)+MID(B2,LEN(B2)-7,2)>24,1,0)&TEXT(MOD(RIGHT(B2,8)+RIGHT(A2,8),1),":hh:mm:ss")

It's now quite a complicated composite formula. I can't help thinking that a few lines of macro code in a VBA User Defined function might be more manageable. But this seems to cover up to 4 digit years.

**daddylonglegs** · 01-12-2012, 07:18 PM

I see a few issues with Richard's solution. I think the days should always show leading zeroes so you'd get 003 rather than 3 for instance. Also the days should never show 365 if the assumption is that there are 365 days in the year 1:364:23:59:59 + 0:000:00:00:01 = 2:000:00:00:00

I think you can use this formula to "sum" A2 and B2

=LEFT(A2,FIND(":",A2)-1)+LEFT(B2,FIND(":",B2)-1)+(RIGHT(A2,8)+RIGHT(B2,8)+LEFT(RIGHT(A2,12),3)+LEFT(RIGHT(B2,12),3)>=365)&":"&TEXT(MOD(INT( RIGHT(A2,8)+RIGHT(B2,8)+LEFT(RIGHT(A2,12),3)+LEFT(RIGHT(B2,12),3)),365),"000")& TEXT(RIGHT(A2,8)+RIGHT(B2,8),":hh:mm:ss")

Originally Posted by JonnieB

Is it possible to construct a formula to sum a range of data in the format (y:ddd:hh:mm:ss) "y:" being of variable length?

Is that an additional requirement, do you want to sum more than 2 of these, e.g. a whole range like A2:A10? If so then you can use this version

=SUMPRODUCT(LEFT(A2:A10,FIND(":",A2:A10)-1)+0)+INT(SUMPRODUCT(RIGHT(A2:A10,8)+LEFT(RIGHT(A2:A10,12),3))/365)&":"&TEXT(MOD(INT(SUMPRODUCT(RIGHT(A2:A10,8)+LEFT(RIGHT(A2:A10,12),3))),365),"000")&TEXT( SUMPRODUCT(RIGHT(A2:A10,8)+0),":hh:mm:ss")

This latter version can be used to sum 2 values as above, like A2 and B2 as long as they are contiguous.....or can be used for any range fully populated with values in this format