Help making date into a decimal number

[SnowBrian]

Hi there.

So I need help figuring out how to make a date into a decimal number. For example, Dec 14 I need to convert to 11.45

Its basically the number of months and days that have gone by in that calender year. So the 11.45 is "11 months" have gone by and the .45 is the days that have gone by in the month of December. (the 14th day divided by 31 the total in December = .45)

So another example would be Feb 3rd. My conversion would be 1.10 (1 month has gone by in the calender and 3 days into the month, 3 / 28 = .10 so Feb 3rd is now 1.10)
June 20th = 5.66

Im creating a form so that my employees wont have to hand write these anymore and reduce all the human errors. Its to calculate income for my borrowers. I want them to be able to enter todays date and have excel convert that date into the decimal form. Then they can enter a Year to Date $ amount and that will calculate a monthly income. < $ amount * decimal date = monthly income YTD>

Thank you!

-Brian

[Ron Coderre]

With
A1: a date
This regular formula returns the value you asked for

Please Login or Register  to view this content.

Is that something you can work with?

[SnowBrian]

Amazing Mr. Ron Coderre. Works perfectly. Thank you Sir.

[SnowBrian]

Hey Ron

Thanks again for the help....but I found out this morning that this formula works for 2010 only. Im checking with my IT dept to see if they can update the rest of my staff from 2003 to 2010, but in the mean time I wanted to see if you knew how to do this formula for 2003 also?

Thanks again!

[ChemistB]

The issue is EOMONTH. It is actually part of the Analysis ToolPak add-in. On the Tools menu> Add ins
Is it there?

[Richard Buttrey]

Hi,

Not sure why you think Ron's offering doesn't work with 2003. It works in my copy.

Does this variation help

Please Login or Register  to view this content.

[SnowBrian]

Thank you everyone for your input.

Richard - I think you might have the add-in already installed that ChemistB is referring to. I need to get with my IT dept to request that add-in to be installed. That beats getting new separate licenses for everybody to be upgraded to 2010.

Thanks Fellas.

[Jonmo1]

Without EOMONTH

Try
=FLOOR(MONTH(A1)-1+DAY(A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),0.01)

[daddylonglegs]

...or you can use this version

=FLOOR(MONTH(A1)-1+DAY(A1)/(42-DAY(A1-DAY(A1)+42)),0.01)

[SnowBrian]

Jonmo - that one works perfectly.

Thanks again to EVERYONE that put their input in. I really appreciate it.

[Jonmo1]

You're welcome.

[Richard Buttrey]

...and just because I hate leaving these things,and without being dependent on Analysis Tool Pak

Please Login or Register  to view this content.