Converting days to Year and Month

%3CLINGO-SUB%20id%3D%22lingo-sub-1348997%22%20slang%3D%22en-US%22%3EConverting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1348997%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI've%20been%20trying%20to%20convert%20number%20of%20days%20into%20years%20and%20months.%3C%2FP%3E%3CP%3EI've%20looked%20up%20some%20formula%20which%20tells%20me%20that%20I%20can%20use%20%3DDATEDIF(0%2C(cell%20with%20no.%20of%20days)%2C%22Y%22)%20to%20generate%20the%20number%20of%26nbsp%3B%20years%20there%20are.%20However%2C%20this%20doesnt%20work%20on%20excel%20when%20I%20tried%20it.%20There%20was%20an%20error%20message%20that%20suggested%20there%20were%20too%20many%20arguments%20in%20the%20equation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20of%20any%20alternatives%20instead%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1348997%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349018%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F642465%22%20target%3D%22_blank%22%3E%40vicneoh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20works%2C%20e.g.%3C%2FP%3E%0A%3CP%3E%3DDATEDIF(0%2C365%2C%22Y%22)%20returns%200%20and%26nbsp%3B%3DDATEDIF(0%2C367%2C%22Y%22)%20returns%201%3C%2FP%3E%0A%3CP%3EIf%20not%20full%20years%2C%20when%20like%3C%2FP%3E%0A%3CP%3E%3DYEARFRAC(1%2C365)%20returns%200.9972%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20above%20is%20for%20Windows%2C%20not%20sure%20about%20Mac%20with%20it's%20shifted%20dates%20scale.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349022%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F642465%22%20target%3D%22_blank%22%3E%40vicneoh%3C%2FA%3E%26nbsp%3BThe%20syntax%20for%20this%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DDATEDIF(%3CSTART%20date%3D%22%22%3E%2C%20%3CEND%20date%3D%22%22%3E%2C%22option%22)%3C%2FEND%3E%3C%2FSTART%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20%22option%22%20is%20Y%2C%20M%20or%20D.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349057%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDidn't%20even%20realise%20you%20could%20use%20DATEDIF%20that%20way.%20Hence%2C%20my%20response%20using%20start%20and%20end%20dates.%20Tried%20yours%20and%20can%20conform%20its%20the%20same%20on%20a%20Mac.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349077%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349077%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20this!%20I%20realise%20the%20issue%20is%20with%20my%20mac.%20Tried%20it%20on%20windows%20and%20it%20works..hmm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349082%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349082%22%20slang%3D%22en-US%22%3EOHHH.%20I%20just%20realised%20it%20was%20an%20error%20in%20my%20syntax!%20It%20works%20too%2C%20on%20my%20mac!%20Thanks%20very%20much%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349729%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349729%22%20slang%3D%22en-US%22%3EMicrosoft%20Excel's%20TEXT%20function%20can%20help%20you%20to%20convert%20a%20date%20to%20its%20corresponding%20month%20name%20or%20weekday%20name%20easily.%20In%20a%20blank%20cell%2C%20please%20enter%20this%20formula%20%3DTEXT(A2%2C%20%22mmmm%22)%2C%20in%20this%20case%20in%20cell%20C2.%20%2C%20and%20press%20the%20Enter%20key.%20And%20then%20drag%20this%20cell's%20AutoFill%20handle%20to%20the%20range%20as%20you%20need.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1349788%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20days%20to%20Year%20and%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1349788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F493704%22%20target%3D%22_blank%22%3E%40Lewis-H%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInitial%20question%20was%20like%3C%2FP%3E%0A%3CP%3EWe%20spent%20456%20days%2C%20how%20many%20full%20months%20is%20it%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20TEXT()%20is%20not%20very%20suitable%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I've been trying to convert number of days into years and months.

I've looked up some formula which tells me that I can use =DATEDIF(0,(cell with no. of days),"Y") to generate the number of  years there are. However, this doesnt work on excel when I tried it. There was an error message that suggested there were too many arguments in the equation.

 

Does anyone know of any alternatives instead?

 

Thanks so much in advance!

7 Replies

@vicneoh 

In general it works, e.g.

=DATEDIF(0,365,"Y") returns 0 and =DATEDIF(0,367,"Y") returns 1

If not full years, when like

=YEARFRAC(1,365) returns 0.9972

 

But above is for Windows, not sure about Mac with it's shifted dates scale.

@vicneoh The syntax for this would be:

=DATEDIF(<start date>, <end date>,"option")

where "option" is Y, M or D. 

@Sergei Baklan 

Didn't even realise you could use DATEDIF that way. Hence, my response using start and end dates. Tried yours and can conform its the same on a Mac.

Thanks for this! I realise the issue is with my mac. Tried it on windows and it works..hmm.

 

OHHH. I just realised it was an error in my syntax! It works too, on my mac! Thanks very much for the help!
Microsoft Excel's TEXT function can help you to convert a date to its corresponding month name or weekday name easily. In a blank cell, please enter this formula =TEXT(A2, "mmmm"), in this case in cell C2. , and press the Enter key. And then drag this cell's AutoFill handle to the range as you need.

@Lewis-H 

Initial question was like

We spent 456 days, how many full months is it?

 

Perhaps TEXT() is not very suitable here.