Average a number into months left in year

[jgryctz]

I am completing leave reports (days off) for my employees. Some of them have more days left than they can carry over for the next calender year and are required to use them by the end of the year. Schedules are posted on a monthly basis. I already have a column that uses an IF formula to show only those people that are >0 (shows the number of days left). What is the formula if I want to calculate the average number of days they must take by the end of the year.

I.E. Mr. Smith has 60 leave days and can carry over 55 so he is 5 over. He has from August until December to use them thats 5 months, therefore, he must use an average of 1 day per month to not lose any days. I am not sure if I need to setup a dummy date cell with the date 12/1/2013 or what... Thanks in advance.

**More easily said, I need to divide a cell value by the number of months left in the year***

[jhren]

Full months left in year...

Please Login or Register  to view this content.

[etaf]

datedif() will count the months left
=DATEDIF(TODAY(),"31/12/2013","M")
and so that returns 5

then you need to use the cell with the number of days left - the cell with the days allowed to take over / the formula datedif

[FDibbins]

or just...
=DATE(2013,12,31)-TODAY()
formatted as number

[jhren]

Ummm.... number of months.

Not number of days.

[FDibbins]

LOL thanks jhren, I guess it pays to read properly

**More easily said, I need to divide a cell value by the number of months left in the year***

[jgryctz]

Thank you all, I thought about that after i sent the post but showing the formula was still a big help, thanks a lot.