LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-25-2012, 10:42 PM   #1
tailinlinux
Member
 
Registered: Oct 2010
Location: Paudpod, Botolan, Zambales, Philippines
Distribution: Mandriva, Ubuntu, Mint, Open Suse, Meego
Posts: 390
Blog Entries: 3

Rep: Reputation: 25
Open Office Calc Formula to Computer Time Duration


Do anyone here knows, formula to compute time duration in calc?
Example:
Time Started Time Finished Duration
08:00AM 09:00 AM = 1 Hour or 1

Thanks in advance.
 
Old 06-26-2012, 07:49 AM   #2
Snark1994
Senior Member
 
Registered: Sep 2010
Distribution: Debian
Posts: 1,632
Blog Entries: 3

Rep: Reputation: 346Reputation: 346Reputation: 346Reputation: 346
Working with A1, B1, C1:
  • Select A1 and B1 and go Format > Cells and change 'Category' to 'Time' and 'Format' to '01:37 PM'.
  • Select C1 and go Format > Cells and change 'Category' to 'Time' and 'Format' to '01:37'.
  • Edit A1 to contain the start time, then edit B1 to contain the end time (both in HH:MM:SS format).
  • Finally, enter '=B1-A1' in C1 and it will display the difference in times correctly.

Hope this helps,

Last edited by Snark1994; 06-26-2012 at 07:52 AM.
 
Old 06-26-2012, 08:09 PM   #3
tailinlinux
Member
 
Registered: Oct 2010
Location: Paudpod, Botolan, Zambales, Philippines
Distribution: Mandriva, Ubuntu, Mint, Open Suse, Meego
Posts: 390

Original Poster
Blog Entries: 3

Rep: Reputation: 25
Quote:
Originally Posted by Snark1994 View Post
Working with A1, B1, C1:
  • Select A1 and B1 and go Format > Cells and change 'Category' to 'Time' and 'Format' to '01:37 PM'.
  • Select C1 and go Format > Cells and change 'Category' to 'Time' and 'Format' to '01:37'.
  • Edit A1 to contain the start time, then edit B1 to contain the end time (both in HH:MM:SS format).
  • Finally, enter '=B1-A1' in C1 and it will display the difference in times correctly.

Hope this helps,
Thanks for reply but this is not working.
I want to know if i can make a formula to computer time duration.
Example:
Time Started A1= 10:00:00 AM
Time End B1= 10:30:00 AM
Duration C1= 30 Mins.
 
Old 06-27-2012, 02:59 AM   #4
tailinlinux
Member
 
Registered: Oct 2010
Location: Paudpod, Botolan, Zambales, Philippines
Distribution: Mandriva, Ubuntu, Mint, Open Suse, Meego
Posts: 390

Original Poster
Blog Entries: 3

Rep: Reputation: 25
Yeah i already did it.
Thanks for the reply.

This is the formula Ive done.
Code:
=B1-A1+IF(A1>B1,1)
 
Old 06-27-2012, 10:37 AM   #5
Snark1994
Senior Member
 
Registered: Sep 2010
Distribution: Debian
Posts: 1,632
Blog Entries: 3

Rep: Reputation: 346Reputation: 346Reputation: 346Reputation: 346
Well... The formula I gave should work with your test data (10:00:00 and 10:30:00). Your formula will show the (positive) difference of the two if the start time is after the end time, which is an unlikely occurrence for well-behaved data - however, I don't know the context of your spreadsheet.

If you consider your problem to be solved, please mark the thread as 'SOLVED'. Thanks
 
Old 06-27-2012, 11:05 AM   #6
suicidaleggroll
LQ Guru
 
Registered: Nov 2010
Location: Colorado
Distribution: OpenSUSE, CentOS
Posts: 5,573

Rep: Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141Reputation: 2141
Quote:
Originally Posted by Snark1994 View Post
Your formula will show the (positive) difference of the two if the start time is after the end time, which is an unlikely occurrence for well-behaved data - however, I don't know the context of your spreadsheet.
It looks like his mod is to add a day in case the start/end crosses the 24hr border.
 
Old 06-28-2012, 01:30 AM   #7
tailinlinux
Member
 
Registered: Oct 2010
Location: Paudpod, Botolan, Zambales, Philippines
Distribution: Mandriva, Ubuntu, Mint, Open Suse, Meego
Posts: 390

Original Poster
Blog Entries: 3

Rep: Reputation: 25
Thanks for all the reply. I am already satisfied on my formula, and i already use it by now.

More powers to LQ.
 
Old 06-28-2012, 02:47 AM   #8
Snark1994
Senior Member
 
Registered: Sep 2010
Distribution: Debian
Posts: 1,632
Blog Entries: 3

Rep: Reputation: 346Reputation: 346Reputation: 346Reputation: 346
Quote:
Originally Posted by suicidaleggroll View Post
It looks like his mod is to add a day in case the start/end crosses the 24hr border.
*facepalm* duhhhh. Sometimes I should remember to switch my brain on. Yeah, that's a more plausible usecase than a start time after your end time...
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Open/Libre Office Formula Editor - How to write reserved symbols? golmschenk Linux - Newbie 2 03-20-2012 09:39 PM
Help Please - Open Office CALC Formula's SangrelX Linux - Software 1 06-23-2009 09:42 PM
open office formula editor Four Linux - Newbie 1 03-30-2006 09:46 PM
how do I display a Linear regression formula using Open Office? izquierdista Linux - Software 2 07-20-2005 06:51 PM
elapsed time formula for open office calc philosophe Linux - Software 1 03-30-2005 05:32 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 09:29 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration