Formatting of hours in Excel spreadsheet
Thread Starter
Join Date: May 2003
Location: Orion
Posts: 8
Likes: 0
Received 0 Likes
on
0 Posts
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?
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?
Join Date: Oct 2006
Location: EGBJ Gloucester
Age: 40
Posts: 103
Likes: 0
Received 0 Likes
on
0 Posts
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 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!
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
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?
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Thread Starter
Join Date: May 2003
Location: Orion
Posts: 8
Likes: 0
Received 0 Likes
on
0 Posts
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
http://www.mrexcel.com/board2/viewtopic.php?t=210370
Thanks to all those who contributed suggestions.
Betelgeuse
Hovering AND talking
Join Date: Feb 2003
Location: Propping up bars in the Lands of D H Lawrence and Bishop Bonner
Age: 59
Posts: 5,705
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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!
Thread Starter
Join Date: May 2003
Location: Orion
Posts: 8
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Join Date: Jan 2001
Location: Stansted, UK
Posts: 44
Likes: 0
Received 0 Likes
on
0 Posts
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!
=(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!