# 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.

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

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

Mate, that is absolutely perfect. Legend!

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!

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?

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

Do you think it's possible?

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1