Mixing Number and Time format

[willie6112]

I’m trying to work with Time data.

I have events that are timed in whole minutes, no decimals. For example, a 50-minute event is entered as 50 (number format) and a 75-minute event is entered as 75 (number format). I average these times and present the result rounded to the nearest whole number (whole minute).

Can I present all these numbers in a time format? Can I present 50 (number format) as :50 (time format), 75 (number format) as :75 (time format), and the average as :63 (time format)? How do I calculate the average in Time format?

How can I add 50 (number format) and :75 (time format)?

How can I enter :50 (time format) without seconds, i.e., :50, NOT 50:00?

Let ne know if that’s not clear and I’ll try to clarify.

 

[barry houdini]

If you enter 50:00 Excel will interpret that as 50 hours. for 75 minutes you can enter either 1:15 or 0:75, both should work.

Cell formatting can be whatever you want, so you can format as h:mm and 75 minutes will display as 1:15 or format as [m] and 75 minutes will display as 75. Note that the cell format doesn't affect how you need to input the data (e.g. with cells formatted as [m] then 75 minutes will just display as 75.....but you can't input it that way)

Time values can be averaged just like any other data

 

[Tetra201]

If you want your numbers to appear with a colon in front of them, just custom-format them as \:00
However, this will not convert the numbers into Excel time values.