+ Reply to Thread
Results 1 to 5 of 5

Formula to calculate time base on a date

  1. #1
    Forum Contributor
    Join Date
    07-15-2011
    Location
    Whitby, Canada
    MS-Off Ver
    Excel 2010
    Posts
    121

    Formula to calculate time base on a date

    Hi all- this is a re-post (post - 2689573) since I did not have any luck the first time around,

    For the purpose of automating vacation time allotment - I am looking for a way to calculate the number of years between dates (ie. the start date of an employee to present) - catch is, the number of applicable 'years' change if the start date is before June 30 Additionally, a new year's worth of vacation time is accrued on January 1st of each year.

    Example - an employee who started on June 30 2011 qualifies for 1 year's worth of vacation by the next day (July 1) and then another year come Jan 1 of the following year....an employee who starts on July 1 does not qualify for 1 year's worth of vacation until January 1 of the following year...hope this makes sense - sample attached
    TIA
    Attached Files Attached Files
    Last edited by Greed; 02-03-2012 at 11:10 AM. Reason: solved

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Formula to calculate time base on a date

    Try this

    =(SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&TODAY())),"ddmm")="0101"))+(MONTH(A2)<7))*8

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,012

    Re: Formula to calculate time base on a date

    Well, you can get the number of months using:

    =DATEDIF(A2,$D$1,"m")/12

    and you can round that to years with:

    =ROUND(DATEDIF(A2,$D$1,"m")/12,0)

    And you can determine whether or not to add 1 using:

    =IF(MONTH(A2)<7,1,0)

    So, putting them together:

    =ROUND(DATEDIF(A2,$D$1,"m")/12,0)+IF(MONTH(A2)<7,1,0)


    However, I don't get the same answers as you so you may need to tweak the rounding


    Hope this helps, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    07-15-2011
    Location
    Whitby, Canada
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Formula to calculate time base on a date

    Nifty - thanks Bob!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,012

    Re: Formula to calculate time base on a date

    @Bob: yes, that is very clever.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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