+ Reply to Thread
Results 1 to 6 of 6

Years Minus Year Months Days

  1. #1
    Registered User
    Join Date
    03-23-2020
    Location
    Sydney, Australia
    MS-Off Ver
    365
    Posts
    20

    Years Minus Year Months Days

    Hi there,

    I am working out my company's Years of service awards, I have a report on thousands of Years of service calculations supplied to me, I then worked out how man days they had as breaks in service to determine there actual years of service.

    So now my issue is calculating this, Attached is my sample sheet Years of Service is in years I then have the breaks in service calculated in Days I converted this in the next column to Years Months Days now need a formula to calculate the years minus the breaks in Years Months Days format.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Years Minus Year Months Days

    Hi AusExcelNewb and welcome to the forum,

    If you leave those numbers in date format it would be a lot easier. Dates are really numbers since 1/1/1900 in excel. I'd start by taking the End date minus the Start date. That would be the total days they worked. Then I'd use the leave dates (End Leave - Start Leave) and subtract that from the Total days worked above. Then I'd divide by 365 to get years and use 30 for months. I'm not sure you can get closer than that as the stupid months have different number of days.

    Hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Years Minus Year Months Days

    In D2:
    Please Login or Register  to view this content.
    Is it you are looking for?
    Quang PT

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Years Minus Year Months Days

    How about:
    =[@[Service years]]-[@[Breaks in Service in days]]/365.25

  5. #5
    Registered User
    Join Date
    03-23-2020
    Location
    Sydney, Australia
    MS-Off Ver
    365
    Posts
    20

    Re: Years Minus Year Months Days

    Thank you, I have sourced the start dates now so this will work

  6. #6
    Registered User
    Join Date
    03-23-2020
    Location
    Sydney, Australia
    MS-Off Ver
    365
    Posts
    20

    Re: Years Minus Year Months Days

    Thanks all for the awesome options this will save me so many hours of work

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Years, Months, Week, Days, Hours, Minutes for Work Days between two dates
    By hecgroups in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-23-2019, 12:09 AM
  2. [SOLVED] Converting years months and days into into total days
    By Aestivator1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2015, 04:35 PM
  3. How do I convert years, months, days back to complete days
    By Nisha Dhawan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2014, 03:21 AM
  4. No of months between 2 different years by year
    By AkilaAnnamalai in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-01-2013, 03:43 PM
  5. Convert days -> Years, Months, Days, Hours, Minutes, Seconds
    By brharrii in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2012, 06:44 PM
  6. [SOLVED] Start Date minus (Years + Months + Days) = ?
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2006, 01:10 PM
  7. [SOLVED] Convert Years to Years/Months/Days
    By [email protected] in forum Excel General
    Replies: 4
    Last Post: 01-04-2006, 11:00 AM

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