Converting a Date into a number consisting of the number of full months and a percentage of the partial month

[Austin-L]

Hello, the title pretty much says it all! I am trying to convert an excel spreadsheet for income calculation for my boss. She wants me to convert a date format into a number format consisting of the total number of full months year to date, and the percentage of the number of days in the current month divided by the total number of days in the month (rounded up to 2 decimal places). --- For example: 8/16/2013 should read as 7.52 -- 2/5/2013 should read as 1.18 --- 11/11/2013 should read as 10.37. Thank you for your help!

 

[Austin-L]

Sorry forgot to mention I'm using Excel 2010

 

[Matty]

Welcome to MrExcel!

Try:

=MONTH(A1)-1+DAY(A1)/DAY(EOMONTH(A1,0))

Matty

 

[Austin-L]

Hey Matty thanks for the help! I think I need to be more specific now The user will enter a date in cell G11 (8/15/2013). I would like for the completed conversion to appear in cell G12 (7.48). How will this change the formula?
Thanks!

 

[Matty]

Hi,

Put the formula I posted in cell G12 and change all of the A1s to G11.

Matty

 

[Austin-L]

I got it! Thank you very much Matty!!!

 

[Matty]

I got it! Thank you very much Matty!!!

Good work. Glad I could help.

Matty

 

[Austin-L]

That is what I did after your initial response (changing the cell from A1 to G11) but what I did wrong was I had the cell formatting set to date instead of number and it was giving me 1/7/1900. I reset the formatting and it worked like a charm!

 

[Matty]

I've done that a few times myself.

One thing I wanted to check though. What should the formula show if we are at the very start of a month or at the very end?

Matty

 

[Austin-L]

I've done that a few times myself.

One thing I wanted to check though. What should the formula show if we are at the very start of a month or at the very end?

Matty

I just tested it and it looks like it is working as expected. If I enter 8/1/2013 in box G11, G12 returns 7.03 which is the desired result. If I enter 8/31/2013, G12 returns 8.00 which is also working fine.