[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Herbivore
General User

Joined: 29 Apr 2008
Posts: 7

 Posted: Wed Apr 30, 2008 8:37 am    Post subject: Date ratios [SOLVED] 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
keme
Moderator

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

 Posted: Wed Apr 30, 2008 9:30 am    Post subject: 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
Herbivore
General User

Joined: 29 Apr 2008
Posts: 7

 Posted: Thu May 01, 2008 3:54 am    Post subject: Date ratios [SOLVED] keme, exactly. Worked just right. Thank you!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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