# Need a formula to calculate holidays from start dates to the current date!

1. ## Need a formula to calculate holidays from start dates to the current date!

Basically, I've a holiday tracker for learners. I have their names in columns a & b, their start dates, then in the top row going across I got the week commencing etc. I need to create a formula that works out how many months it has been since a learner has been here, times that by 1.5 (they get 1.5 days a month holiday) then - (minus) the days they have already taken. is there ANY WAY of doing this? My file is attached.  Register To Reply

2. ## Re: Need a formula to calculate holidays from start dates to the current date!

Hi, I hope it helps.

Edit: the formula subtracts the sum of numbers on columns F-BE: probably you need to subtract only the number in column D.

=DATEDIFF(C5,TODAY(),"M")*1.5-D5

Saluti  Register To Reply

3. ## Re: Need a formula to calculate holidays from start dates to the current date!

Mate, that is absolutely perfect. Legend!  Register To Reply

4. ## Re: Need a formula to calculate holidays from start dates to the current date!

Hi Guys

Same again basically, i'm keeping the formula, but need an extension, can't see it happening though. Basically, from today, 01/08/2011, learners now have 2 days holiday per month, but their holidays are carried over, so i need a formula that calculates the following: (from their start date UP TO 31/07/2011, per month +1.5, + from 01/08/2011 to "TODAY", +2)

Is this possible? Any help would be much appreciated!  Register To Reply

5. ## Re: Need a formula to calculate holidays from start dates to the current date!

=DATEDIF(C5,E184,"M")*1.5-SUM(G5:BG5)-E5+(DATEDIF(D184,TODAY(),"M")*2)

Got this formula, problem is, it adds 2 days every month from 01/08/2011, btw E184=31/07/2011 and D184=01/08/2011!

Any suggestions?  Register To Reply

6. ## Re: Need a formula to calculate holidays from start dates to the current date!

Do you think it's possible?

Regards  Register To Reply