Search code examples
excelfunctionformula

Datedif issue - convert number of days to "--Years--Months--Days" format

I should convert number of days to "--Years--Months--Days" format. I am mostly calculating the percentages of working days.

=DATEDIF(B2,C2,"y")&" years "&DATEDIF(B2,C2,"ym")&" months "&DATEDIF(B2,C2,"md")&" days"

works well for date to date. Here, I need numbers of days. in to "--Years--Months--Days" format.

e.g: 10 Sep 2021 to 01 Mar 2023 (546 days) 75% of 546 is 409.5 I need to calculate how many years months days of 409.5 days. Is it possible?

Solution

  • Try,

    =DATEDIF(0,DATEDIF(B2,C2,"D")*0.75,"Y") & " Years, " & DATEDIF(0,DATEDIF(B2,C2,"D")*0.75,"YM") & " Months, " & DATEDIF(0,DATEDIF(B2,C2,"D")*0.75,"MD") & " Days"