Date serial to real number

JoeSalmi

Board Regular
Joined
Jan 31, 2011
Messages
128
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
711
Hi Joe,
This formula will return how many days in the month for cell A1.
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
Would you be able to use that for your math problem?
HTH, Slink
 
Upvote 0

JoeSalmi

Board Regular
Joined
Jan 31, 2011
Messages
128
That does get me closer it looks like but then how would I also get Feb:Dec?

Also when I use the current formula IF(NOW()>=A2,IF(MONTH(NOW())=MONTH(A2), DAY(NOW()),EOMONTH(A2,0)),"") it allows me to calculate the current rate at the time I open the document. Example being Today is the 8th so she is 7 days behind. Your formula only shows me the # of days in the month minus one.
 
Upvote 0

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
ADVERTISEMENT
If A1 contains a date, then =DAY(EOMONTH(A1, 0)) returns the number of days in the month.
 
Upvote 0

JoeSalmi

Board Regular
Joined
Jan 31, 2011
Messages
128
If A1 contains a date, then =DAY(EOMONTH(A1, 0)) returns the number of days in the month.

okay well I guess I didnt figure it out so I just edited my comment.

ETA:

When I try to multiply the number of days (minus1) in a month that has already passed such as October it is giving me the serial number instead of the number of days.
Example:
Instead of 30*5=150 it which you see the 30 and the 5 but the 30th day of October 2011 is really the 40846th day. so my math comes out to 40846*5=204230... How can I get it to multiply by the number of days instead of the serial number of the day???
 
Last edited:
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
ADVERTISEMENT
This formula will return how many days in the month for cell A1.
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
You can simplify your formula a little bit by realizing the 0th day of a month is the last day of the month before...

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
 
Upvote 0

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
okay well I guess I didnt figure it out so I just edited my comment.
I think it would serve you well to concisely restate exactly what you have and exactly what you want.
 
Upvote 0

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
711
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
 
Upvote 0

JoeSalmi

Board Regular
Joined
Jan 31, 2011
Messages
128
I think it would serve you well to concisely restate exactly what you have and exactly what you want.

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
 
Upvote 0

Forum statistics

Threads
1,196,021
Messages
6,012,904
Members
441,739
Latest member
Jeezer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top