Add hh:mm:ss with mm:ss

[icsupt]

Have a situation where we need to add hours:minutes:seconds. Sounds simple enough. But, the information was imported and I'm not sure of the formatting, and some rows have hrs:min:sec, but other rows have only min:sec.

Thanks in advance.

 

[BattleDroid42]

Use the DateAdd() function; see the Visual basic help file.

Remember, wherever you go...there you are.

 

[SkipVought]

Hi,

Time Values are in fractions of a day. FORMATTING of a Time Value is a DISPLAY feature.

To VIEW a Time Value, change the cell FORMAT (I'm assuming Excel) to General. It it does not change to a NUMBER, you do not have REAL TIMES.

To add Date/Time values, you just ADD. If A1 & A2 contain Time Values...
[tt]
=A1+A2
[/tt]
The only anomylie that you might see is IF the TIME VALUES that you sum, exceed 1. You can DISPLAY hours beyond 24 hours in a day by a cell format of [h]:mm:ss

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[byundt]

If your data is in text format (rather than a date/time serial number) then you can add it with a worksheet formula like:
=A1+("00:" & A2)
In this formula, A1 looks like h:mm:ss and A2 like m:ss

If you want the formula to figure out your data type, then try:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,"00:" & A1,A1)+IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))=1,"00:" & A2,A2)