Previous Topic Next Topic

Ages and difference between them in years:months

Closed
jfdiow Posts 1 Registration date Monday January 13, 2014 Status Member Last seen January 13, 2014 - Jan 13, 2014 at 12:17 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 14, 2014 at 01:41 AM
Hi,

I would like to enter data in the form 12:06 meaning 12 years and 6 months and then work out the difference between two such cells with the answer in the same format.
i.e. 21:02 and 26:05 should give a difference of 5:03

I can do this as a complicated formula if I put the years in one cell, the months in another- multiply the years by 12 and add them to the months to give a decimal number then reverse the process after subtraction etc. but it would be much easier if such a cell format existed.

Please note this isn't trying to use dates (dd,mm,yyyy) , just on straight ages of years and months.

Thank you very much for any help you can give

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 14, 2014 at 01:41 AM
in excel if you type 26:11 it automatically takes it as hrs and minutes that is 26 hours and 11 minutes and 0 sec and not as years and months

I was trying to write a formula but it becomes complicated to me. some expert may give you a formula

FOR E.G. select sheet1
so if BEFORE entering data you format columns A and B as text and THEN ONLY enter data

now enter A1 as 26:01 and B1 as 26:05

right click the TAB of the sheet and click view code
in the window that comes up copy this EVENT CODE 


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim yr1 As Integer, yr2 As Integer, result, yr As Integer, mnth As Integer
Dim mnth2 As Integer, mnth1 As Integer
If Target.Column <> 1 Then GoTo exiting
On Error GoTo exiting
Application.EnableEvents = False
Target.Offset(0, 2).Clear
yr2 = CInt(Left(Target.Offset(0, 1), 2))
yr1 = CInt(Left(Target, 2))


If CInt(Right(Target, 2)) > CInt(Right(Target.Offset(0, 1), 2)) Then
yr = yr2 - yr1 - 1
mnth2 = CInt(Right(Target.Offset(0, 1), 2)) + 12
mnth1 = CInt(Right(Target, 2))

mnth = mnth2 - mnth1
Else
yr = yr2 - yr1
mnth1 = CInt(Right(Target, 2))
mnth2 = CInt(Right(Target.Offset(0, 1), 2))

mnth = mnth2 - mnth1
End If
result = yr & " " & "years" & " " & mnth & " " & "months"
Target.Offset(0, 2) = result
exiting:
Application.EnableEvents = True


End Sub

now go and select A1 and see c1
if by change A1 is already selected select cell in some other column
and again and select A1
what do you see in A1

as an experiment
enter A2 21:11
in B2 enter 26:05

select B1 and see what you get C2

do some more experiments like this where month number in column B is more or less than month number in column A

PLEASE GIVE FEEDBACK.
0