Leading Zeros & Time Displays Using Calc or Base

DeeCeeWis · General User Joined: 26 Aug 2011 Posts: 8

Hello - I'd like to create either a spreadsheet or database that includes the various stats from my workout gadgets - a BodyBugg, heart rate monitor, pedometer and my Wii. Including the date, time of day, name of the workout and a note field, there are 20 fields.

I'd like to enter the amount of time I'm working out and see it as 1:01 for 1 hour and 1 minute, or :43 for forty-three minutes, but when I format for time, it displays as the actual time - how can I get it to display as 1:01 and for it to know I mean hours and minutes?

When I enter an amount of time less than an hour, can the zero before the colon not be displayed?

I'd prefer to create the database info in calc since I'm pretty clueless about the database functions, and then at some point merge it into a template I've created in writer. Hopefully someone will be able to help me and thanks! DC

peterroots · Posted: Sat Aug 27, 2011 1:20 pm Post subject:

format the cell using one of the time formats. There is one listed as HH:MM which does exactly what you want - if you have a column of figures, formatted as HH:MM and you sum them (for example) the hours and minutes add up as they should.
HH:MM:SS is also available if you want seconds but the HH:MM format stores the result with seconds in it - watch out, it includes them in calculations but you don't see them so 3:30:30 displays as 3:30 but two cells containing the same value (or multiply the cell by two) and you get 7:01
_________________
Kubuntu 11.10 LibreOffice3.4.4

DeeCeeWis · General User Joined: 26 Aug 2011 Posts: 8

Hello - so what I don't want is to see the AM or PM, because it's not actually the time I want to see, but the amount of time. When I look in the bar at the top, it always has the AM or PM there, so I wondered if there was a way to prevent that or if there's a command or function that just shows the amount of time and not what time it is. Thanks for getting back to me! DC

[quote="peterroots"]format the cell using one of the time formats. There is one listed as HH:MM which does exactly what you want - if you have a column of figures, formatted as HH:MM and you sum them (for example) the hours and minutes add up as they should.
HH:MM:SS is also available if you want seconds but the HH:MM format stores the result with seconds in it - watch out, it includes them in calculations but you don't see them so 3:30:30 displays as 3:30 but two cells containing the same value (or multiply the cell by two) and you get 7:01[/quote]

RPG · Posted: Sun Aug 28, 2011 4:15 am Post subject:

Hello

I think use a formatted control in stead of a time control.

Romke

peterroots · Posted: Sun Aug 28, 2011 12:10 pm Post subject:

formatting a time as HH:MM does not show am or pm but does seem limited to 12 hours. [HH]:MM on the other hand also does not show am or pm and copes with hundreds of hours and correctly adds up the minutes and hours.

Sorry I did not spot the 12 hour limit on HH:MM originally.
_________________
Kubuntu 11.10 LibreOffice3.4.4

DeeCeeWis · General User Joined: 26 Aug 2011 Posts: 8

Hello - I continue to have this problem. I have a Calc spreadsheet with cells formatted as

[H]:MM

which if I type in 0:16 produces 12:16:00 AM in the formula bar area. How can I get a display of 0:16 and not the time of 12:16 AM?

Hi RPG - I'm sorry I don't know what a formatted control is, but I'm going to do a search for it and see if i can find some information on it. Until then, any additional suggestions would be appreciated. Thanks, DC

peterroots · Posted: Mon Sep 05, 2011 12:22 pm Post subject:

In LibreOffice 3.3.3 a cell formatted as [H]:MM displays as 0:16 if that is what is entered (on the formula bar it shows up as 00:16:00) nowhere is am or pm displayed.

What are you using OpenOffice or LibreOffice as there may be a difference between the two in how they do this?
_________________
Kubuntu 11.10 LibreOffice3.4.4

DeeCeeWis · General User Joined: 26 Aug 2011 Posts: 8

I'm using open office org 3.3.0

and yes, my formula bar always shows the AM or PM no matter how I format the cell.

peterroots · Posted: Wed Sep 07, 2011 1:00 pm Post subject:

maybe worth giving LO a try to see if it works for you
_________________
Kubuntu 11.10 LibreOffice3.4.4

RPG · Posted: Thu Sep 08, 2011 12:42 am Post subject:

Hello

Maybe you have text in your field and not a time or date. Text start with this character: '
This is a high comma and is used in Calc.

Romke