Ages and difference between them in years:months

[Closed]
Report
Posts
1
Registration date
Monday January 13, 2014
Status
Member
Last seen
January 13, 2014
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.