Calculating Date Formula

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Mar 31, 2005

• #2

Problem is that "months" have a variable number of days (28, 29, 30 or 31), so you cant define a month.

Depending on how accurate you need the result to be, you could simply subtract the dates and divide by (365/12) and use a round function to convert to a whole number.



HTH

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Mar 31, 2005

• #3

Do you know where the dates are or do you need to find them?

Assuming you know where the first date is in column A (in A1) but you have to find the last date in column B

=DATEDIF(A1,LOOKUP(9.99999999999999E+307,B:B),"m")

will give you the number of whole months between the dates

 

Upvote 0

P Sitaram

Well-known Member

Joined

Jun 24, 2003

Messages

1,974

• Mar 31, 2005

• #4

If you want difference in months between the corresponding cells of the two cols: use MONTH(B1)-MONTH(A1).

By `last date in column 2', if you mean the last row in col2 use:

=DATEDIF($A$1,INDEX(C4:C6,MATCH(9.99999999999999E+307,C4:C6)),"M")

 

Upvote 0

J

JABWootton

Board Regular

Joined

Dec 16, 2004

Messages

94

Office Version

1. 2019

Platform

1. Windows

• Mar 31, 2005

• #5

Column A Column B Column C

Start Finish Duration

Feb-02 Sep-07 Need the Total No of Months between Col A and B
Oct-04 Sep-07
Feb-02 Jun-06
Oct-04 Sep-07 If i did it manually this Cell would read 36
Sep-03 Sep-06

 

Upvote 0

P Sitaram

Well-known Member

Joined

Jun 24, 2003

Messages

1,974

• Mar 31, 2005

• #6

=DATEDIF(A1,B1,"m")

 

Upvote 0

J

JABWootton

Board Regular

Joined

Dec 16, 2004

Messages

94

Office Version

1. 2019

Platform

1. Windows

• Mar 31, 2005

• #7

Works

Thank You
Easy when u know how

 

Upvote 0