Date ratios [SOLVED]

[Herbivore]

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.

[keme]

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.

[Herbivore]

keme, exactly. Worked just right. Thank you!