Converting days to Year and Month

Copper 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.