Calculate Years, Months Weeks

**mycon73** · 03-01-2011, 09:58 PM

Hi,

I have a formula that can calculate the difference between two dates & give me a result like:

# Years(s), # Month(s)

# = Number or result difference between dates.

Here's the formula I'm using to get above results:

=IF(S11="","0 Years(s), 0 Month(s), 0 Day(s)",IF(S14="TBD",DATEDIF(S11,TODAY(),"y")&" year(s), "&DATEDIF(S11,TODAY(),"ym")&" month(s)",IF(S14>1,DATEDIF(S11,S14,"y")&" year(s), "&DATEDIF(S11,S14,"ym")&" month(s) ")))

Now, I would like to add in Weeks, but not certain what the "week" code is to give:

# Years(s), # Month(s), # Week(s)

=IF(S11="","0 Years(s), 0 Month(s), 0 Day(s)",IF(S14="TBD",DATEDIF(S11,TODAY(),"y")&" year(s), "&DATEDIF(S11,TODAY(),"ym")&" month(s)",IF(S14>1,DATEDIF(S11,S14,"y")&" year(s), "&DATEDIF(S11,S14,"ym")&" month(s) "&DATEDIF(S11,S14,"mw")&" Week(s) ")))

This, "mw" is wrong n my formula to find or calculate weeks. What should be here or is there a better way to calculate the difference between two dates & still get:

# Years(s), # Month(s), # Week(s)

Thanks

**canapone** · 03-02-2011, 10:22 AM

Hi, a possibility

Number of weeks:

=INT(DATEDIF(S11,S14,"md")/7)&" Week(s) "

Number of days

=DATEDIF(S11,S14,"md")-INT(DATEDIF(S11,S14,"md")/7)*7&" Day(s) "

Regards

**mycon73** · 03-02-2011, 11:29 AM

Hi Canapone,

Thanks for the suggested formulas.

I added in your, =INT(DATEDIF(S11,S14,"md")/7)&" Week(s) " into my formula or to the end of it & I'm getting:

# Years(s), # months(s)

Instead of:

# Years(s), # Month(s), # Week(s)

Here's the formula:

=IF(S14="TBD","TBD",IF(S11="","0 Years(s), 0 Month(s), 0 Day(s)",IF(S14="TBD",DATEDIF(S11,TODAY(),"y")&" year(s), "&DATEDIF(S11,TODAY(),"ym")&" month(s)",IF(S14>1,DATEDIF(S11,S14,"y")&" year(s), "&DATEDIF(S11,S14,"ym")&" month(s)",INT(DATEDIF(S11,S14,"md")/7)&" Week(s) "))))

Now, if I use, =INT(DATEDIF(S11,S14,"md")/7)&" Week(s) "

I get:

0 Week(s)

I think we are close, but not quite close enough to get:

# Years(s), # Month(s), # Week(s)

Any other suggestions?

Thanks again for the assistance.

**canapone** · 03-02-2011, 11:46 AM

Hi, zero week means - simply put: pardon my English - we have less than 7 days.

I would add the days: 0 week and 5 days for istance.

Or you could add a condition "if number of week is zero", display nothing or only the days or zero week and number of days.

Just for sharing some ideas.

Regards

**daddylonglegs** · 03-02-2011, 12:02 PM

Try this version

=IF(S11="","0 years(s), 0 month(s), 0 week(s)",DATEDIF(S11,IF(S14="SBD",TODAY(),S14),"y")&" year(s), "&DATEDIF(S11,IF(S14="SBD",TODAY(),S14),"ym")&" month(s) "&INT(DATEDIF(S11,IF(S14="SBD",TODAY(),S14),"md")/7)&" week(s) ")