Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table data display in cell | Excel Discussion (Misc queries) | |||
how to display subjects taught by a specific teacher upon selection of the teacher name in a drop down box | New Users to Excel | |||
How to display results to certain number of significant figures | Excel Discussion (Misc queries) | |||
How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |