Formula to convert text hh:mm:ss to time

[Oscar Martinez]

I am trying to get the average of two times with format hh:mm:ss which are in text. These two values are put together using concatenate. Do you know how can I convert the text to time, so I can perform an average?

 

[SirJB7]

Hi, Oscar Martinez!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

These two values are put together using concatenate.

And regarding your issue, how are they together, without space, with another separator string?
Post a few samples of your input data or better indeed consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

 

[Oscar Martinez]

Thanks for the quick reply. Attached you will find the spreadsheet that I am using for my testing.

The concatenate function is used as follows:

CONCATENATE((I30*24+J30),":",K30,":",L30)

The values are being pulled from cells I30 to L30.

In column S you will see an example of what the final calculation should look like.

 

[Kevin@Radstock]

Perhaps

 

[oldchippy]

Hi,

Try this

=I30+VALUE(J30&":"&K30&":"&L30)

=I31+VALUE(J31&":"&K31&":"&L31)

BTW Format your average cell to hh:mm:ss

 

[SirJB7]

Hi, Oscar Martínez!

Keeping your Q column formulas try this at Q30 and copy down to Q31:
=FECHANUMERO(CONCATENAR((I30*24+J30);":";K30;":";L30))+HORANUMERO(CONCATENAR((I30*24+J30);":";K30;":";L30)) -----> in english: =DATEVALUE(CONCATENATE((I30*24+J30),":",K30,":",L30))+TIMEVALUE(CONCATENATE((I30*24+J30),":",K30,":",L30))

Or to make it simple:
=I30+J30/24+K30/24/60+L30/24/60/60

Regards!

 

[oldchippy]

Hi Oscar,

Can't the time values be entered as actually times in your spreadsheet, rather than in separate cells. It would make it much more simpler.

 

[Oscar Martinez]

Thank you all for your feedback.

I used a combination of the average and time functions as suggested by Kevin@Radstock. It worked!!!

=AVERAGE(I30,I31) + AVERAGE(TIME(J30,K30,L30),TIME(J31,K31,L31))

 

[NARAYANK991]

Hi Oscar ,

Try this :

=CONCATENATE((I30*24+J30),":",K30,":",L30)+0

=CONCATENATE((I31*24+J31),":",K31,":",L31)+0

in Q30 and Q31. The formula in Q32 will work.

Narayan