Formating text and Date on a cell.

[3drian]

Hi there guys, quick questions here, I am sure its not difficoult.

I am trying to average some events duration, however the cell is formated in general likes this:

0 Hrs 00 Mins 00 Secs
(all in text)

How could I be able to format that in order to be able to add two cells together or averages some of them.

Thanks in advance.

 

[iliace]

In order to perform date/time calculations, Excel has to recognize your entry as an accepted date/time format. There are several available, but yours isn't one of them.

Therefore, you'll have to convert it first. Here's an example of how you can do that. I'm taking the entry in cells A1 and A2, converting them in B1 and B2, and then averaging them in B3. Note, cells B1:B3 are formatted as "h:mm:ss" to display time rather than a fraction.

Excel Workbook
	A	B
1	1 Hrs 01 Mins 20 Secs	1:01:20
2	0 Hrs 15 Mins 30 Secs	0:15:30
3		0:38:25
Sheet

 

[mfexcel]

You need a formula to convert the text into TIME format first.

Try, next to your text (assume it is in A1)
in B1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," Secs","")," Mins ", ":")," Hrs ",":")+0
Format the result to TIME format you need.

Hi there guys, quick questions here, I am sure its not difficoult.

I am trying to average some events duration, however the cell is formated in general likes this:

0 Hrs 00 Mins 00 Secs
(all in text)

How could I be able to format that in order to be able to add two cells together or averages some of them.

Thanks in advance.

 

[3drian]

Thanks guys, I really appreciate it, It works!
However one small issue, its not averaging it for me.
I can add them if I do =A1+A2; but if I try SUM(A1,A2) it does not work. Any ideas? I tried sum because neither that or Average works.
Thanks again

 

[iliace]

A1 and A2 are your original inputs. Excel treats them as text; as far as Excel is concerned, there is no date value or any kind of numeric information in those cells. No calculation can be performed on those.

Either of the suggested formulas give you the corresponding time value for your original inputs. So you have to average those. In my example, you would sum and/or average the B1 and B2 cells, as you see in B3 of my example. That's where the calculation is. mfexcel's formula will give you the same result in most situations, wherever you put that formula will be the new time value. You have to average those, not the original inputs.

 

[mfexcel]

Exactly

A1 and A2 are your original inputs. Excel treats them as text; as far as Excel is concerned, there is no date value or any kind of numeric information in those cells. No calculation can be performed on those.

Either of the suggested formulas give you the corresponding time value for your original inputs. So you have to average those. In my example, you would sum and/or average the B1 and B2 cells, as you see in B3 of my example. That's where the calculation is. mfexcel's formula will give you the same result in most situations, wherever you put that formula will be the new time value. You have to average those, not the original inputs.