OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Leading Zeros & Time Displays Using Calc or Base

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
DeeCeeWis
General User
General User


Joined: 26 Aug 2011
Posts: 8

PostPosted: Fri Aug 26, 2011 11:25 pm    Post subject: Leading Zeros & Time Displays Using Calc or Base Reply with quote

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
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Sat Aug 27, 2011 1:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
DeeCeeWis
General User
General User


Joined: 26 Aug 2011
Posts: 8

PostPosted: Sat Aug 27, 2011 4:44 pm    Post subject: Reply with quote

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]
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sun Aug 28, 2011 4:15 am    Post subject: Reply with quote

Hello

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

Romke
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Sun Aug 28, 2011 12:10 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
DeeCeeWis
General User
General User


Joined: 26 Aug 2011
Posts: 8

PostPosted: Mon Sep 05, 2011 11:58 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Mon Sep 05, 2011 12:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
DeeCeeWis
General User
General User


Joined: 26 Aug 2011
Posts: 8

PostPosted: Mon Sep 05, 2011 12:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Wed Sep 07, 2011 1:00 pm    Post subject: Reply with quote

maybe worth giving LO a try to see if it works for you
_________________
Kubuntu 11.10 LibreOffice3.4.4
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Sep 08, 2011 12:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group