+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    04-16-2010
    Location
    Cardiff
    MS-Off Ver
    Excel 2003
    Posts
    5

    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.
    Thanks in advance.
    Attached Files Attached Files
    Last edited by De4no; 04-16-2010 at 07:32 AM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    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
    Attached Files Attached Files
    Last edited by canapone; 04-16-2010 at 08:04 AM.

  3. #3
    Registered User
    Join Date
    04-16-2010
    Location
    Cardiff
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Mate, that is absolutely perfect. Legend!

  4. #4
    Registered User
    Join Date
    04-16-2010
    Location
    Cardiff
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #5
    Registered User
    Join Date
    04-16-2010
    Location
    Cardiff
    MS-Off Ver
    Excel 2003
    Posts
    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. #6
    Registered User
    Join Date
    04-16-2010
    Location
    Cardiff
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Any updates on this people?

    Do you think it's possible?

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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