input and display data in cell in h:mm format without changing to time

**k9mikep** · 02-25-2010, 04:15 PM

I am wondering if someone could help me with a formula that I can put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32
+B36:AF36 in a h:mm format.

When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.

Example:

B20= 1:15 (1h15m) NOT 1:15:00 AM

B24= :30 (30min)

B28= :45 (45min)

B32= 1:45 (1h45m) NOT 1:45:00 AM

B38= 4:15 (4h15min)

I have attached a copy of the form for better clarification.

It doesnt seem to be complicated, but it's certainly a lot more than I thought.

Thanks in advance.

**darkyam** · 02-25-2010, 04:24 PM

In Excel, there's no difference between 1:15 and 1:15 AM, save for formatting. You can go to format cells, then custom, then [h]:mm for these cells. Does that help?

**k9mikep** · 02-25-2010, 04:33 PM

No, it does not, Ive tried that along with different variations of formats. It comes out all the same, when I input 1:15, it displays that in the cell but says that it is 1:15:00 AM in the formula bar. I would like it to simply display 1:15 without changing region language and clock settings or putting an apostrophe before the values since Im sure other people using this form wont remember to put that apostrophe. Is there a way to format the cell so that it puts the apostrophe in for you?

**NBVC** · 02-25-2010, 04:35 PM

If you don't want to see the 0 in front of :15 (as 0:15), then it needs to be entered as text.. AFAIK

You can select all the "Gray" cells and format them as text... then you don't need to enter in the apostrophe's...

The sum cell, though could be: =TEXT((0&B20)+(0&B24)+(0&B28)+(0&B32)+(0&B36),"h:mm")

and it would show the 0 if less than 1 hour, but not the AM or PM in the formula bar.