Need Formula for Date Conversion

L

Lyle

Hello. I am fairly familiar with Excel and have been racking my brain trying
to figure a formula out. Here is the scenario. The military uses dates in
this order yyyymmdd ( i.e. 20050401 is April 1, 2005). I have two colums of
data in this fomat and I want to subtract one from the other to give me
atleast a total number of months and years. For example 19971015 minus
19600212. The long hand answer is 37y08m03d. I have 3190 of these I must
compute. Any help out there?
 
F

Fredrik Wahlgren

Lyle said:
Hello. I am fairly familiar with Excel and have been racking my brain trying
to figure a formula out. Here is the scenario. The military uses dates in
this order yyyymmdd ( i.e. 20050401 is April 1, 2005). I have two colums of
data in this fomat and I want to subtract one from the other to give me
atleast a total number of months and years. For example 19971015 minus
19600212. The long hand answer is 37y08m03d. I have 3190 of these I must
compute. Any help out there?
Click to expand...

I have created a simple UDF which works for the example you gave. Start the
Visual Basic Editor, insert a module and paste this

Public Function MilDateDiff(ByVal date1 As String, date2 As String) As
String
Dim d1 As Date
Dim d2 As Date
Dim sDiff As String
d1 = DateSerial(Left(date1, 4), Mid(date1, 5, 2), Right(date1, 2))
d2 = DateSerial(Left(date2, 4), Mid(date2, 5, 2), Right(date2, 2))

sDiff = CStr(Year(d2) - Year(d1))
sDiff = sDiff + "y"

sDiff = sDiff + CStr(Month(d2) - Month(d1))
sDiff = sDiff + "m"

sDiff = sDiff + CStr(Day(d2) - Day(d1))
sDiff = sDiff + "d"

MilDateDiff = sDiff
End Function


If you enter =MilDateDiff(19600212, 19971015) this function returns 37y8m3d.
This is slightly different from 37y08m03d

/Fredrik
 
A

Alan Perkins

One way:
Select the first column
With the column selected, select Data | Text to columns from the menu
Select next twice
Select the data radio button on the third screen and "YMD" from the dropdown
Repeat with the second column

You should have two columns in your normal date format (dd/mm/yyyy in UK,
mm/dd/yyyy in USA)
Format the columns, using a custom format "YYYYMMDD" if you want to retain
the date format.

Now enter your formula, for example in c1 "=A1-B1", and format that cell YY
MM DD and copy down the column.

HTH

Alan P.
 
K

Kassie

Is this what you are looking for?

=LEFT(A1,4)-LEFT(B1,4)&"y "&MID(A1,5,2)-MID(B1,5,2)&"m
"&RIGHT(A1,2)-RIGHT(B1,2)&"d"
 
J

Jim

There is no single formula that is appropriate for determining the period
between two dates in all situations. This is because the neither number of
days in a year nor the number of days in a month is constant. For some
purposes, such as determining a person's age, it may be appropriate to ignore
the effects of a leap year. For other purposes, the extra day in a leap year
may be important.

Consequently, you have to determine the purpose of your calculation and the
logic that supports that purpose before you design your formula (or write
your custom function using VBA). For example, if you were calculating the
period from February 28, 2005 to March 31, 2006, the correct answer might be
(a) 1 year, 1 month and 3 days, or (b) 1 year and 1 month, or (c) 365 + 31
days = 1.0849 years, depending on the purpose of the calculation. Each of
these possibilities would result in a different formula.

Depen
 
Top