Date Calculation Start Date Minus Year, Month and Days

March 5, 2009 by vba excel
Filed under: Excel Date Functions 
Sponsor

Excel Date Functions Question:

In Microsoft excel date functions, suppose I have a regular date, like: 3 February 1922, and now, I want to subtract: 86 years, 6 months, and 23 days.
1) How can I do this in excel date functions or formulas?
2) What is the correct answer for this excel date function?
3) What are the limitations in Excel functions for this type of calculation?

Thanks much in advance.

Excel Date Functions Answer:

#1 Assuming you have 03/02/1922) in A1
86 in C1
6 in D1
23 in E1

1) The excel formula is:
=DATE(YEAR(A1)-C1,MONTH(A1)-D1,DAY(A1)-E1)

2) The correct answer cannot be calculated by using microsoft Excel formulas (see limitations), so excel vba will be the solution for this.
3) Excel formulas function computes dates as an integer number of days since 01/01/1900


#2 You could get the correct value in microsoft excel vba date functions using dateserial and output it to a cell as text though as the VBA dates start in the year 100. Code follows (minus robustness & error handling)

Sub Test()
Debug.Print SubtractTimeFromDate(DateSerial(1922, 2, 3), 86, 6, 23)
End Sub
Public Function SubtractTimeFromDate(startDate As Date, years, months,
days) As String
Dim endDate As Date
endDate = DateSerial(Year(startDate) - years, Month(startDate) -
months, Day(startDate) - days)
SubtractTimeFromDate = Format(endDate, “dd/mm/yyyy”)
End Function

  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • E-mail this story to a friend!
  • Live
  • MisterWong
  • Propeller
  • StumbleUpon
  • Technorati
  • TwitThis
  • YahooMyWeb
  • Print this article!

Rate This Tips:

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 3.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

excel date calculations,date calculation in excel,excel date minus date,date minus date in excel,how to minus dates in excel,excel date calculation,excel today minus date,how to minus date in excel,minus dates in excel,date calculations in excel,excel date minus month,date minus date excel,excel minus dates,date minus date,,excel date minus,excel date minus 1 year,minus date in excel,excel date calculation formula,excel minus date,date calculation excel,excel current date minus date,excel date minus days,0,excel minus 1 year,excel vba date calculations,start of month excel,mss,excel date minus years,excel remove day from date,excel start date,minus date excel,remove day from date in excel,date minus days,date minus excel,excel date minus 1 month,excel date minus hours,excel dates calculation,excel formula date minus date,excel minus one month,excel date minus one year,excel date minus year,excel formula today minus date,excel minus month,excel minus one year,microsoft excel date calculations,date minus in excel,excel minus date from date,excel start of month,minus dates excel



Related Excel Tips

Tags: , , , , , , ,

Comments

9 Comments on Date Calculation Start Date Minus Year, Month and Days

  1. lEE on Mon, 3rd Aug 2009 1:35 am

    2. How to I calculate the total number of days employees worked with excel? I have start dates and end dates.

  2. jatin on Fri, 25th Dec 2009 10:51 am

    3. i want date - date so plz give me how to make a result
    exmple:

    Colam a
    Start DATE
    1/12/2009

    Colam b
    FORM DATE
    16/12/2009

    how to make a coloum a - coloum b

  3. VIJAY POOJARI on Mon, 8th Feb 2010 9:13 am

    4. Dear sir,

    Ref , would like to know wehtr we can use date 12/01/2010 - 30 days = upto which date will be vaild, can i know the formula immediately in Excel sheet.

    Tks / vijay
    Egi Nxv.

  4. Sanjay on Thu, 11th Mar 2010 3:31 pm

    5. i want to calculate the length of service for a employee

    Example
    01/03/1972 - 30/04/2010 = 48 years , 01 month & 29 days
    date = dd/mm/yyyy

  5. Sanjay on Thu, 11th Mar 2010 3:40 pm

    6. sorry
    some correction to my previous qsn.
    i want to calculate the length of service for a employee

    Example
    01/03/1972 - 30/04/2010 = 38 years , 01 month & 29 days
    date = dd/mm/yyyy

  6. Sanjay Pradhan on Fri, 12th Mar 2010 8:45 am

    7. How i calcutate the age in below format

    birth date - today = ___year :___ months:___ days

    example:

    birth date= 11/02/1972
    today= 12/03/2010

    age= 38Year : 01 Months : 01 days

  7. Mins on Mon, 29th Mar 2010 3:30 pm

    8. Hi Sanjay, this is what I did to my Excel:-

    A1: 1-Mar-1972
    A2: 30-Apr-2010 … or if you want to insert current day, simply type =Today()
    A3: =A2-A1

    Once done, do a Custom Format for cell A3. The custom format is as follows:-
    yy “Year : “mm” Month : “dd” Days”

    Hope these help

  8. Arjun Kr. Chaudhary on Thu, 6th May 2010 8:53 am

    9. Please give full exampel in details following answer , how to show in date format.

    kindly help me.
    (Arjun Kr. Chaudhary)
    date : 06.05.10

    Mins on Mon, 29th Mar 2010 3:30 pm

    Hi Sanjay, this is what I did to my Excel:-

    A1: 1-Mar-1972
    A2: 30-Apr-2010 … or if you want to insert current day, simply type =Today()
    A3: =A2-A1

    Once done, do a Custom Format for cell A3. The custom format is as follows:-
    yy “Year : “mm” Month : “dd” Days”

    Hope these help

  9. Sanjay on Thu, 6th May 2010 4:38 pm

    10. A1: 1-Mar-1972
    A2: 30-Apr-2010 … or if you want to insert current day, simply type =Today()
    A3: =A2-A1

    Once done, do a Custom Format for cell A3. The custom format is as follows:-
    yy “Year : “mm” Month : “dd” Days”

    in the above procedure

    15.05.2010-15.03.1972 = 37 yrs , 12 month & 30 days
    but the actual result is 38 yrs.

Have another excel answer or questions for this problem ?
Feel free to post it here..