How to convert Days into Months and Days?

Hi

I have number of days as raw data, how do I convert it into Months and Days using Excel formula?

eg... 120 days = 4 months 0 days

Thanks.

Best regards,
Gilbert

It works perfectly.. thanks... just wonder what if we need year? (ie. 366 days = 1 years 0 months 1 days)

Thanks for the help in advance

or

PHP Code: 

=TEXT(A1,"y \y\ear m \mon\t\h\s d \da\y\s") 


I don't think that will work snb, months will always show as 1 too many.....

If 120 days is exactly 4 months then does that mean you are assuming a month is 30 days? In that case isn't 366 days 1 year and 6 days? Here's one possible option

=DATEDIF(0,A1,"y")&" years "&DATEDIF(0,A1,"ym")&" months "&DATEDIF(0,A1,"md")&" days"

Originally Posted by daddylonglegs

I don't think that will work snb, months will always show as 1 too many.....

If 120 days is exactly 4 months then does that mean you are assuming a month is 30 days? In that case isn't 366 days 1 year and 6 days? Here's one possible option

=DATEDIF(0,A1,"y")&" years "&DATEDIF(0,A1,"ym")&" months "&DATEDIF(0,A1,"md")&" days"

This formula doesn't seem to work perfectly, eg. when you put 366 days.. it will show 0 year 11 months 31 days. When you have 367 days.. it will display 1 year 0 months 1 day. Just wonder if there is something we can do to improve this formula?

Originally Posted by arthurbr

Datedif used with startdate 0 does provide for problems because XL assumes incorrectly that 1900 is a leap year

The question is : what do you call a year? If 1 year = 365 days, =quotient(A1,365)&" years" will give the whole number of years
Which result do you need ? Please provide an example?

The purpose of this is to determine the length of service. I agree with daddylonglegs that by using quotient, it would mean all months are equal (ie. 30 days), which is not true. This would mean that 365 days would return 12 months 5 days.

I did try to use quotient (A1, 365) followed by Mod(A1,30) but this doesn't seem to return the right figure. I am using 400 days as example, it will return 1 and 10, which I find it not reflective. Any thoughts how to improve further?

Of course I know what I want. As explained above, I wanted to determine the length of service at the end of the year. My data contains the employee date joined. What I did was to calculate the number of days since the staff joined (using 31/12/2011 - date joined). After that, I wanted to display how many years, months and days instead of only days so that one can easily know how long the staff has worked.

If you actually have the hire date then rather than calculating the days in service from that and then converting to years months and days it's better to use DATEDIF directly with the hire date, because then you are capturing the actual period rather than a notional period of days, e.g. with hire date in B1 this will give the period up to the current date

=DATEDIF(B1,TODAY(),"y")&" years "&DATEDIF(B1,TODAY(),"ym")&" months "&DATEDIF(B1,TODAY(),"md")&" days"

OK.. let me try if it works nicely. What if I want to compare date joined with future date (say 31/12/2011) instead of today? Am I suppose to put 31/12/2011 in one of the cell and then replace the formula TODAY() with that cell?

Yes, in DATEDIF you can put the dates in as cell references or any other function that will return a date (as long as the first date is prior to the second one - if it isn't you get #NUM! error)

...so yes, simplest way is to put your end date in C1 for instance and use

=DATEDIF(B1,$C$1,"y")&" years "&DATEDIF(B1,$C$1,"ym")&" months "&DATEDIF(B1,$C$1,"md")&" days"

or you can put in the date directly using DATE function, e.g.

=DATEDIF(B1,DATE(2011,12,31),"y")&" years "&DATEDIF(B1,DATE(2011,12,31),"ym")&" months "&DATEDIF(B1,DATE(2011,12,31),"md")&" days"

I recommend the former as it will be more easily "updateable" for next year or whenever.....