Formatting of hours in Excel spreadsheet

[Betelgeuse]

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?

[robdesbois]

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!

[BOAC]

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?

[Keef]

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

[Betelgeuse]

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

[Whirlygig]

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

[Keef]

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!

[Betelgeuse]

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.

[737TG]

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!