Date serial to real number

[JoeSalmi]

I have a formula in which I am asking for the number of days in a month to be printed in B3 but when I try to do math from the # of days it gives me the date serial number instead. Can I get some help please.

In cell A1 I have the date 1/1/2011 formatted as "yyyy"
In cell A2 I have =DATE(YEAR($A$1),"1","1") formatted as "mm"
In cell B2 I have =IF(NOW()>=A2,IF(MONTH(NOW())=MONTH(A2), DAY(NOW()),EOMONTH(A2,0)),"") formatted as "dd"

Now in another sheet I have math going on but for the sake of this post lets just say that my math is now in cells H5:H16
using this formula: =IF(I5="Y",B2-1,"")
(now what this is saying it that if my tenant is late on rent I want to it to multiply the late charge (in cell H2 ($5))by the number of days that have pasted minus 1 day.

So for example January has 31 days. Rent is due on the first however everyday after the 1st a $5 late fee is accumulated. So in cell H5 it displays the number of days they are late. In this case it is 30 days. However if you do the format to a number it shows me 40573 instead of 30. So when I try to multiply the number of days they are late by the late fee it gives me $1,646,208,902.00

Seems my tenants might be a little upset by this so I was wondering if anyone could give me a hand.

 

[JoeSalmi]

Thanks for the tip Rick!

Joe, maybe it would help if you just tell us what you need to do without using the formulas that you are already using. For example, "I want cell B1 to tell me the difference between today's date and the last day of last month" or whatever . . . maybe it's just too late tonight for me to wrap my brain around it but sometimes it helps to state it more simply without using the formulas in your explanation. Slink

I understand that.

What I want to do is calculate the number of days my tenant is late on rent. My lease agreement states that rent is due on the first and for everyday after the 1st there is a $5 charge. I want to calculate the current amount due at the time I open the spreadsheet. I don't want to have to enter in anything other then a "y" to state that their rent is late.

 

[Rick Rothstein]

I want to convert the serial number of a day to a number such as the current number of days passed or present
October is passed so it would tell me 31 but with the same formula I want it to tell me that today is the 9th

I'm not sure about anyone else, but this is still not clear to me. Here is a suggestion that I think will help... pick out 5 or more typical values that cover a representative range of dates and show them to us along with the result you would like the formula to produce for them. That way we can better see what you are trying to describe to us in words.

 

[JoeSalmi]

top right > Download original

https://docs.google.com/open?id=0B93rLjRrGdTEN2U5YTI1NTYtYjExNC00YWYzLWIyYTctMmY2NTg4YzVmMWRl

 

[JoeSalmi]

top right > Download original

https://docs.google.com/open?id=0B93rLjRrGdTEN2U5YTI1NTYtYjExNC00YWYzLWIyYTctMmY2NTg4YzVmMWRl

Picture might help

http://i44.tinypic.com/14c435y.jpg

 

[Rick Rothstein]

Try replacing the formula you have in H14 with this...

=H2*DAY(--the formula you currently have in H14 goes here, without the equal sign, of course--)

 

[JoeSalmi]

Try replacing the formula you have in H14 with this...

=H2*DAY(--the formula you currently have in H14 goes here, without the equal sign, of course--)

#NAME?
I see what your trying to do though.

 

[Rick Rothstein]

#NAME?
I see what your trying to do though.

Telling us you got a #NAME? error doesn't really tell us anything. Copy/paste the formula that gives you that error into a response message here so we can see what you attempted to do.

 

[SlinkRN]

Hi again Joe,
The picture and the download really helped - I am playing with it but no answer yet, we'll keep plugging away though! Interesting problem!! Slink

 

[SlinkRN]

Wait a minute!!! Rick already had the answer - using his suggestion, put
=H$2*DAY(IF(I14="Y",B11-1,""))
into cell H14.
That works for me in your downloaded sheet Joe, the result in H14 is $150.
It even works for November when you fill down. Great job Rick!!!

 

[JoeSalmi]

Wait a minute!!! Rick already had the answer - using his suggestion, put
=H$2*DAY(IF(I14="Y",B11-1,""))
into cell H14.
That works for me in your downloaded sheet Joe, the result in H14 is $150.
It even works for November when you fill down. Great job Rick!!!

You're right it does work. However I needed to change it up some. I ended up with this formula

=H$2*DAY(B11)-1

Of course I did a ton of other moving things around but I did get it... Thanks to all the help guys.