Round Months

[Boeskool]

Hi All,

To track employees' service time I have 2 dates (A1=start date and B1=end date). After substracting B1-A1/365.25 the outcome could e.g. be 10 years 5 months, but could also be 10 years and 7 months (cell is formatted on number). What I'd like to do is, have a formula round down months <7 to, in this example, 10 full years and months >7 to 11 full years.

Thx a lot.

Boeskool

 

[AlphaFrog]

This will round the years up or down at the six month mark.

=DATEDIF(A1,EDATE(B1,6),"y")

I wasn't sure if you intended to round at the 6 month mark (half a year) or the seven month. If you want to round at the seventh month, change the red 6 to a 7.

 

[Spurious]

=IF((A1-B1)/365.25-ROUNDDOWN((A1-B1)/365.25,0)>(7/12),ROUNDDOWN((A1-B1)/365.25,0)+1,ROUNDDOWN((A1-B1)/365.25,0))

=IF(MOD((A1-B1)/365.25,ROUNDDOWN((A1-B1)/365.25,0))>(7/12),ROUNDDOWN((A1-B1)/365.25,0)+1,ROUNDDOWN((A1-B1)/365.25,0))

Those two work for me.

 

[pjmorris]

a couple of thoughts:

1. Your desired output is unlikely to be sensistive to leap years and so I wouldn't worry about the 0.25 in your expression.
2. It doesn't matter how you've formatted the 2 dates, they will still be stored as the number of days since 1 jan 1900.
3. Your expressions for rounding look a little strange: I presume you mean to round down for <7 months and to round up for >=7 months.

My suggestion would be to replace you =(B1-A1)/365.25 with =YEARFRAC( B2, B1, 3 ), which returns the fraction of the years between the two dates. If you then format that cell as having no decimal places Excel will do the rounding for you.

Hope this helps.

 

[Boeskool]

Thank you all, solutions worked for me.