Date Calculation Start Date Minus Year, Month and Days
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
Rate This Tips:
(1 votes, average: 5.00 out of 5)
Loading ...Incoming excel search terms
excel date calculations,excel date minus date,date calculation in excel,date minus date in excel,how to minus dates in excel,excel date calculation,how to minus date in excel,minus dates in excel,excel today minus date,excel date minus month,date minus date excel,date calculations in excel,excel date minus,minus date in excel,excel minus dates,excel vba date calculations,excel date minus days,start of month excel,0,excel date minus hours,excel date minus years,date minus date,excel minus date,excel date minus 1 year,excel minus date from date,excel minus month,excel minus one month,excel start of month,remove day from date in excel,excel date minus year,excel minus 1 year,1h,date minus in excel,excel date minus 1 month,excel date year,excel remove day from date,minus date excel,minus one date from another in excel,,date minus days,excel current date minus date,excel date calc,excel date minus one year,excel date minus today,excel dates calculation,excel formula date minus date,excel minus one year,excel minus years,excel start date,minus 1 year excel
Related Excel Tips
Comments
3 Comments on Date Calculation Start Date Minus Year, Month and Days
-
lEE on
Mon, 3rd Aug 2009 1:35 am
-
jatin on
Fri, 25th Dec 2009 10:51 am
-
VIJAY POOJARI on
Mon, 8th Feb 2010 9:13 am
How to I calculate the total number of days employees worked with excel? I have start dates and end dates.
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
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.
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project (5.00 out of 5)
- Copy from Worksheet (5.00 out of 5)
- Change Worksheet Tab Colors (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Quick Way Determine The First and Last Business Day of the Month (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Date Calculation Start Date Minus Year, Month and Days (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
