|
Display Age
I have a spreadsheet in which I have one column where I enter a persons D.O.B. and in the next column it contains the formula to work out and display their age, however I'd like to be able to just enter the D.O.B. and for the age to be calculated and displayed in the same column as the info is entered, is this at all possible? -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
1 way is:
=NOW()-DATE(1956;11;23) format cells as custum yy "sparty" skrev: I have a spreadsheet in which I have one column where I enter a persons D.O.B. and in the next column it contains the formula to work out and display their age, however I'd like to be able to just enter the D.O.B. and for the age to be calculated and displayed in the same column as the info is entered, is this at all possible? -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
sparty,
Not without losing the DOB entry - there is no formatting solution. Otherwise, you could use an event to overwrite the DOB with the Age formula using the cell entry as one of the arguments. HTH, Bernie MS Excel MVP "sparty" wrote in message ... I have a spreadsheet in which I have one column where I enter a persons D.O.B. and in the next column it contains the formula to work out and display their age, however I'd like to be able to just enter the D.O.B. and for the age to be calculated and displayed in the same column as the info is entered, is this at all possible? -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
Thanks Skrev, couldnt get that to work. Bernie, it doesnt matter if I lose the DOB entry as once calculated all I want to show is the age and for it to auto update. Could you please explain your solution a little more thanks. -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
sparty,
For example, to convert DOB entered into column B, copy the code below, right-click the sheet tab, and select "View Code" and paste the code into the window that appears. You may need to change the Target.NumberFormat = "mm/dd/yyyy" depending on your Version of Excel. Definitely works in the US.... HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 2 Then Exit Sub If Not IsDate(Target.Value) Then Exit Sub Application.EnableEvents = False Target.NumberFormat = "mm/dd/yyyy" Target.Formula = "=DATEDIF(DATEVALUE(""" & Target.Text & """),TODAY(),""y"")" Target.NumberFormat = "0" Application.EnableEvents = True End Sub "sparty" wrote in message ... Thanks Skrev, couldnt get that to work. Bernie, it doesnt matter if I lose the DOB entry as once calculated all I want to show is the age and for it to auto update. Could you please explain your solution a little more thanks. -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
This is a better version - if the column width is too small, the other version gives an error:
Also, the column should be formatted for dates so that IsDate doesn't throw you out.... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 2 Then Exit Sub If Not IsDate(Target.Value) Then Exit Sub Application.EnableEvents = False Target.Formula = "=DATEDIF(DATEVALUE(""" & _ Format(Target.Value, "mm/dd/yyyy") & """),TODAY(),""y"")" Target.NumberFormat = "0" Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... sparty, For example, to convert DOB entered into column B, copy the code below, right-click the sheet tab, and select "View Code" and paste the code into the window that appears. You may need to change the Target.NumberFormat = "mm/dd/yyyy" depending on your Version of Excel. Definitely works in the US.... HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 2 Then Exit Sub If Not IsDate(Target.Value) Then Exit Sub Application.EnableEvents = False Target.NumberFormat = "mm/dd/yyyy" Target.Formula = "=DATEDIF(DATEVALUE(""" & Target.Text & """),TODAY(),""y"")" Target.NumberFormat = "0" Application.EnableEvents = True End Sub "sparty" wrote in message ... Thanks Skrev, couldnt get that to work. Bernie, it doesnt matter if I lose the DOB entry as once calculated all I want to show is the age and for it to auto update. Could you please explain your solution a little more thanks. -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
Once again thanks Bernie it works a treat, however theres one problem I've noticed and that it will only work once so if I delete the contents of a cell and input data (DOB) again in just displays the excel number/date value, is there a way round this? -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
Display Age
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub If Target.Column < 2 Then Exit Sub If Target.Value = "" Then Target.NumberFormat = "mm/dd/yyyy" Exit Sub End If If Not IsDate(Target.Value) Then Exit Sub Application.EnableEvents = False Target.Formula = "=DATEDIF(DATEVALUE(""" & _ Format(Target.Value, "mm/dd/yyyy") & """),TODAY(),""y"")" Target.NumberFormat = "0" Application.EnableEvents = True End Sub "sparty" wrote in message ... Once again thanks Bernie it works a treat, however theres one problem I've noticed and that it will only work once so if I delete the contents of a cell and input data (DOB) again in just displays the excel number/date value, is there a way round this? -- sparty ------------------------------------------------------------------------ sparty's Profile: http://www.excelforum.com/member.php...o&userid=31306 View this thread: http://www.excelforum.com/showthread...hreadid=563674 |
| All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com