Formating text and Date on a cell.

3drian

New Member
Joined
May 14, 2013
Messages
23
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.

:)
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
AB
11 Hrs 01 Mins 20 Secs1:01:20
20 Hrs 15 Mins 30 Secs0:15:30
30:38:25
Sheet
 
Upvote 0
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.

:)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top