View previous topic :: View next topic 
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 datalimited 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 


keme Moderator
Joined: 30 Aug 2004 Posts: 2910 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: =(A2B2)/(C2B2)
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 


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! 

Back to top 




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
