How to enter time w/out colon while keeping the time format?

**olle** · 03-25-2009, 11:51 AM

Hi experts,

I am new to this forum, and was brought here by a problem that I just can’t find the solution to. I’ve spent a bunch of hours with google, trying to find a solution to my problem but I simply can’t find anybody who had the same question anywhere. Although I did a search here and found a very very similiar question.

What I wanna do is to type in a number of minutes and seconds (minutes:seconds), like the following: “1245” or “1” and automatically get it to display as “12:45” and “00:01”, respectively. This I can do with a simple format change, but then I loose the time format and when I sum up the different times I have, I will just get numbers not related to the 60s/h time system that we have (00:55+00:10 will be 00:65 and not 01:05). The similair question that was on this forum was the exact question except s/he wanted the format to be hours:minutes - I need minutes:seconds. So that solution doesn’t help me, I’m just looking for the same thing but with hours:minutes.

The other question, that with the hours:minutes, can be found here if you want to see how they solved that problem:

http://www.excelforum.com/excel-misc...ring-time.html

Would be very grateful for your help.

/Olle

**DonkeyOte** · 03-25-2009, 11:59 AM

removed in preference to below thread

**DonkeyOte** · 03-25-2009, 12:09 PM

Adjusting things slightly...

If entering 5500 for 55 mins 0 secs & 1000 for 10 mins 0 secs then a Custom Format of:

00\:00\:00

Should display as 00:55:00, 00:10:00

Summing

=SUMPRODUCT(--TEXT(A1:A2,"00\:00\:00"))

Formatted to [hh]:mm:ss should result in 01:05:00

Does that work for you ?

**olle** · 03-26-2009, 04:16 AM

Hi,

Thank you that did work. I just had to change a colon to a semi-colon, for some reason. It is amazing how easy it is once you see how you do it, thanks a lot!

I do have one more thing I want to do though, and I can't figure that out either. What I am doing is listing a bunch of times (mm:ss) such as (eg. 00:59, 10:15, 03:56) and so on, and on the bottom I want to have an average. At the moment I have an AverageIf, telling the average if the numbers are greater than zero. However, this average doesn't seem to realize that it's time I want. For example, if one time is 00:01 and another 01:01, the average should be 31 since it's a total of 62 seconds. But the average I get now is 51. In order for it to be 31, I need to change the "01:01" to "61", which is not my idea of how I want it.

Does anybody know how to solve this?

And thank you again...

**DonkeyOte** · 03-26-2009, 04:54 AM

Assuming this follows on from before, let's assume our data range is A1:A10 and our values are as follows:

A1: 1 (entered 1 for 1 second)
A2: 101 (entered 101 for 1 minute and 1 second)
A3 to A10: blank or 0

A1:A10 formatted as Custom Format of: 00\:00\:00 to show integers as though they were entered as Time... ie A1 becomes 00:00:01 and A2: 00:01:01

It then follows that
A11: AVERAGEIF(A1:A10,">0",A1:A10)
will return 51 ... given the underlying values remain integers, ie 102 / 2 = 51 ... this is key A1:A10 are integers displaying as time they are not time values per se.

To do this in time format you need to be a little more creative as per the previous Summation formulae, ie:

A11: =AVERAGE(IF(A1:A10,--TEXT(A1:A10,"00\:00\:00")))
committed with CTRL + SHIFT + ENTER
A11 formatted to [mm]:ss should show 00:31
the above is an array formula and essentially forces the integers in A1:A10 to actual TIME values prior to Averaging (in similar vein to previous Sumproduct example)

NOTE: the above is based on UK regional settings so you will need to alter the above to meet Euro settings (ie colon, semi-colon, comma discrepancies etc..)

**olle** · 03-26-2009, 05:06 AM

Thank you so much! Amazing!

**blmholland** · 08-08-2013, 08:35 PM

I would like to piggy back off the previous question. I want to Subtract time instead of adding and retain the time format. How can I change this formula work to for me:

=SUMPRODUCT(--TEXT(A1:A2,"00\:00\:00

Thanks for any help.

**FDibbins** · 08-08-2013, 08:42 PM

hi blmholland and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**blmholland** · 08-08-2013, 08:52 PM

Thanks FDibbins....sorry for breaking the rules already.

**FDibbins** · 08-09-2013, 12:07 AM

Thats ok, you need to start your own thread though

How to enter time w/out colon while keeping the time format?

LinkBack

Thread Tools

Rate This Thread

Display

How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Re: How to enter time w/out colon while keeping the time format?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions