Formatting of hours in Excel spreadsheet
 

I keep my flight times in an excel spreadsheet.

The country in which I fly requires that I enter times in hh:mm format. I have no trouble in summing the times in hh:mm format, but cannot get the totals to display in the format that I want.

Specifically, I want to display the “thousands” separator. Eg, my totals show as 15245:56 whereas I want to display as 15,245:56. I.e hh,hhh:mm instead of hhhhh:mm.

I’ve tried for days to use the custom number format to display the thousands separator in the hh:mm format, but so far it eludes me.

Anyone know how to do this?

Betelgeuse,

I think this is a windows setting, not a format setting. Go to Start menu, control panel, regional settings and it should be somewhere under there; can't remember where though, and mine's hiding at the moment!

I have a vague memory somewhere that you cannot do this with the custom hh:mm choice and that you will need to write the values to two separate adjacent cells and set the numeric in the hours to show the comma?

You can't do it with the hours:minutes in Excel as-is.

Judicious extracting of hours and minutes, and then pasting strings together would do it.
Here's a scruffy example that shows how.

The key bit is that the numbers are formatted as text after the initial few lines.

I've assumed your hours are at least a 4-digit number or you wouldn't be bothering.

I'm sure all that lot could be wrapped up in a single formula, but I need to get some sleep ;)

I've found a pretty reasonable way to do it in another forum. It's not exactly what I wanted, but it looks like an actual format is not available in Excel, so this is the next best solution.

http://www.mrexcel.com/board2/viewtopic.php?t=210370

Thanks to all those who contributed suggestions.

Betelgeuse

An alternative would be to use two columns; one for hours and one for minutes. Then use the following formulae to total the columns assuming the hours are in column A and minutes in column B (assuming a listing of 20 different times to be added)

Total at bottom of minutes column
=SUM(B1:B20)-(ROUNDDOWN((SUM(B1:B20)/60),0)*60)

And total at bottom of hours column =SUM(A1:A20)+(ROUNDDOWN(SUM(B1:B20)/60,0))

Hope that helps

Cheers

Whirls

Hey - I'd not noticed that ROUNDDOWN (or ROUNDUP) function before! I've spent all these years adding a 5 in the last place and taking the integer :( Old habits!

Thanks for the suggestions Whirlygig and Keef, but I haven't found a better way than the VB script above. I can still enter times in hh:mm format and once setup, it's all painless from there on.

I'm surprised this question hasn't come up on this forum before - doesn't everyone use a spreadsheet for their logbook? But I guess it's only relevant to those recording times in hh:mm format.

You could also convert your times into decimal:

=(MINUTE(A1)/60)+HOUR(A1) [A1 is the cell you wish to convert]

ie. This converts 2:29 into 2.48

Quick and easy and much easier to work with decimal hours


Cheers!