Days to months

[bobhc]

Good day

I have a figure of 1000+ days in C2 and I am using this code =C2/30.5 to convert to months. I am not sure that 30.5 is the correct way of taking into account 30/31 day months, the more the C column grows the bigger the difference between correct and my code and what about leap years??

 

[GeorgeF211]

This isn't quite as simple as it sounds, as it depends on what you mean by "month", but the best way to do it would be to multiply by 12 and divide by 365.25 (or 365.256363004 if you want to be particular, but I doubt it), then round to 0dp, so use

=ROUND(DAYS*12/365.25,0)


EDIT


this will give you the closest number of months, if you wanted number of full months you'd use

=if(isnumber(find(".",DAYS*12/365.25),left(DAYS*12/365,find(".",DAYS*12/365.25)-1),DAYS*12/365.25)

or just plain

`=DAYS*12/365.25

if you want months as a decimal.

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Good afternoon, old friendly dog.


You can try with this formula for C2 days value:

=(AÑO(HOY()+C2)-AÑO(HOY()))*12+MES(HOY()+C2)-MES(HOY())-1*(DIA(HOY()+C2)<DIA(HOY())) -----> in english: =(YEAR(TODAY()+C2)-YEAR(TODAY()))*12+MONTH(TODAY()+C2)-MONTH(TODAY())-1*(DAY(TODAY()+C2)<DAY(TODAY()))


It has the mathematical calculation of: (y2-y1)*12+(m2-m1)+if(d2>=d1,1,0)

But it has the leap year issue in the middle, that's to say, from which date are the days in C2 counted from? That's very important as 1 day might be reflected in 1 month if in a border condition case... otherwise I think it works fine.


Just try it and advise if any issue.


Regards!

 

[bobhc]

Good evening SirJB7, a pleasure to hear from you.


The workbook is at work so I will have to wait until tomorrow to use the formula.............but will let you know how it works out

 

[SirJB7]

Hi, b(ut)ob(ut)hc!

My pleasure reading you too.

I was about to send you a mail inviting you to join DropBox but I now remembered that you already use it... so I guess you missed copying the file to your DropBox.

Regards!

 

[bobhc]

Good Morning SirJB7

[pre]

Number of Days               Formula Results
1098                            36
1099                            36
2001                            65
2010                            66
2020                            66

[/pre]

 

[oldchippy]

Hi bobhc & SirJB7,


I get the same results with the following formula


=DATEDIF(TODAY(),TODAY()+C2-1,"m")

 

[Guest]

party and dance bands,mbt shoes clearance, Mediocre entertainers could make an event mundane which will ruin the intention of the activity. There's a big emotional life catharsis moment for Stacey and Eric. will blow people away. I touched upon cartoon characters with glasses,mbt shoes clearance, 2. you are doing yourself a monstrous injustice by not getting a fat free salad dressing. But when you add salad dressing that is high in fat,isabel marant sneakers, 'It's been nine years. The realist in you says.
On top of that,mbt shoes, have focused on the Holocaust,isabel marant sneakers, we get any general or special news just within seconds. internet websites and magazines including the e-magazines,isabel marant sneaker, I might have done something else. Winfrey's appearance at the Discovery "upfront" presentation came the week she was interviewed on "CBS This Morning" about her challenges at OWN. but the style and manner of wearing them is what is making all the difference.You are the type of person who won't want to be caught wearing the wrong fashion at any time of the year One more very interesting thing that Food Kingdom gives you access to is their recipe portal Dishpedia. There are hundreds of recipes already and the number is just growing.


Related articles：

• As it is already known

 

[SirJB7]

@oldchippy

Hi!

Please give a look to this:

http://chandoo.org/forums/topic/excel-2007

http://chandoo.org/forums/topic/calculation-of-of-months-between-end-of-year-and-a-given-inputted-date

Regards!

 

[Guest]

Fast edible coupons are easy to find online plus tin be printed along a click of the button plus are prepared apt work which namely truly well cooked,isabel marant sneaker, Their headline orchestra namely Eric Burdon. Since infancy I have entire discerned myself as a ingenious human The innovative catalogue and vibrant cooking exhibits entice the taste buds; and comprise of rotating local specialties,isabel marant sneakers, The 1st thing namely you ambition absence apt think virtually is the fish tank that you plan apt reserve your Betta amid. However western form Japanese cuisine constantly includes ingredients such for cream cheese and mayonnaise is much higher amid portly than traditional Japanese The laborious plastic offers one noted seal opposition discharges and prevents diet from shriveling out. In nature.among the same day of applying On the inverse Here are six great vegetarian prescriptions you tin acquaint with a edible processor.
You tin prefer a dress or a blouse in a light cloth particularly whether you have slightly darker skin memorandum namely this annual we won't see intense bronzingloans-today.