custom cell format needed

[cosmicgrooves]

I have a custom format that I use on a daily basis.

I right click the cell/cells, select Format, and choose custom. Under Type, I enter 0":"00. If the time is 4:50 PM, I enter 450 and the cell formats it to 4:50. It looks like time, but to excel, it is still four hundred fifty. This makes it difficult if I want to calculate an average time.

Is there a way to create a custom cell format that when I enter 450, it formats it to time and 4:50. The spreadsheet is only used within a 12 hour basis, so AM and PM or military time is not necesarry.

I tried h":"mm, but that did not work.

Any ideas?

[Stuart Farr]

Hi, have you tried hh:mm?

[cosmicgrooves]

I just tried that.

When I entered 123, it came back as 00:00. I also tried hh":"mm, but it too came back 00:00.

[Stuart Farr]

I'm not aware of a way to do what you are wanting. If you use the format I suggested and entered 4:50 in the cell, Excel will recognise that as a time and you can do your averages.

[mikerickson]

This UDF might do what you want.

Put this in a normal module.

Please Login or Register  to view this content.

and this in the ThisWorkbook code module.

Please Login or Register  to view this content.

CellEntry() returns the last text typed into the cell that calls the UDF CellEntry.

To demonstrate:
Enter =CellEntry() in a cell.
Select that cell, it will look empty and =cellentry() will be in the formula bar
Type "cat" and press Return.
The cell will show "cat", but the formula bar will show =cellentry().
Typing into the cell has set the return value of the CellEntry function to "cat".
It is one way to have a cell hold both a formula and a value entered by the user.

In your case, put
=TIMEVALUE(MID("000"&cellentry(),LEN(cellentry()),2)&":"&RIGHT(cellentry(),2))
in any cell.
Entering 1123 will set the value of CellEntry to "1123", and after being evaluated by the formula, the value in the cell will be the serial time 11:23 AM (0.53125).

Entering 930 puts 9:30 AM into the cell, which can be formatted to your taste.
Entering 1458 puts 2:58 PM in the cell.
Entering 035 returns 12:35 AM; 2435 - 12:35 AM tomorow

This can be done with as many cells as you like.

I hope this helps.

[Stuart Farr]

OK, I've spent some time playing with this and assuming that cell G5 was your input cell, put the following formula in another cell. This works for me and you can get the averages if you have more than one entry.

=VALUE(IF(LEN(G5)=4,LEFT(G5,LEN(G5)-2)&":"&RIGHT(G5,2),LEFT(G5,LEN(G5)-2)&":"&RIGHT(G5,2)))

Hope this helps