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

Date ratios [SOLVED]

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


Joined: 29 Apr 2008
Posts: 7

PostPosted: Wed Apr 30, 2008 8:37 am    Post subject: Date ratios [SOLVED] Reply with quote

How to fill a cell with the answer to the question, "What is the ratio between a given date and time and the total number of days (to the minute) in a given month?"

Purpose: A spreadsheet to keep track of data usage on one's data-limited account such that on any given day of the month, the ratio of time passed in the month is compared to the the ratio of the current usage figure obtained from one's account info at the ISP.

For example:
On 23 Mar 08 at 17:55, when 3644.71MB had been used of a 4800MB allotment, the date position ratio would be about 0.74 (23/31) and the usage 0.76 (3644.71/4800).

It would be convenient to be able to plug in a date and time along with the current usage and see these two ratios side by side. As long as the date ratio is less, one is ahead of the game but if the usage figure is larger, one needs to slow down, pay attention to the websites visited, postpone a download, etc.

I just can not figure out which functions or combination of functions are needed to make the date ratio more exact by factoring in the time of the day.


Last edited by Herbivore on Thu May 01, 2008 3:55 am; edited 1 time in total
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Wed Apr 30, 2008 9:30 am    Post subject: Reply with quote

Date/time values are linear, so you can do simple maths directly.
  • First you need current time to the minute: in A2 you enter the function =NOW() (I mostly prefer leaving row 1 for headings...)
  • In B2 you want the start of this month ("the first"): =DATE(YEAR(A2);MONTH(A2);1)
  • In C2 you want the start of next month: =DATE(YEAR(A2);MONTH(A2)+1;1)
  • The length of the month is C2 - B2. The time passed of this month is A2 - B2.
    In D2 you want the ratio: =(A2-B2)/(C2-B2)

You can pull all of this into a single formula, but that would mean calling a volatile function (NOW()) several times in one calculation, which can yield strange results because the value changes during calculation. With this "segmented" approach it is also easier to locate errors.


Last edited by keme on Thu May 01, 2008 9:12 am; edited 1 time in total
Back to top
View user's profile Send private message
Herbivore
General User
General User


Joined: 29 Apr 2008
Posts: 7

PostPosted: Thu May 01, 2008 3:54 am    Post subject: Date ratios [SOLVED] Reply with quote

keme, exactly. Worked just right. Thank you! Very Happy
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 Calc 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